Hello SQL Gurus,

I have a value for a column. How do I find database, Table and Column has the Value. What can be the query that I can use to locate relevant Database.Table.Column for the given value in the SQL Server?
For eg:-
I have a value '123' now I need to find which Database.Table.Column has the given value.

Thanks in advance

Just curious - In what scenario does it happen ?

USE master
GO

CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS

BEGIN TRY
DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE '

SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE '' + @stringToFind + '' OR '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
EXEC (@sqlCommand)
PRINT @sqlCommand
END TRY

BEGIN CATCH
PRINT 'There was an error. Check to make sure object exists.'
PRINT error_message()
END CATCH

Thanks Dev_ I ran your skript and got 'Command(s) completed successfully.'

The skript provided by 'Dev_', creates SP and then you have to execute SP with input value sto get results..

try below skript- ( 1 quick search gave me this....), just run in each database or you can add code to run in all DB at once...

But depending upon the size of DB and data in table, this will take some time

@stylish - I have used once in the past...We had a process that we used to synch data from multiple tables between two or more sources. tables were divide into groups. One group kept failing complaining about data type and just gave the value out. Since this was in early stage of process, we didn't have enough error handling to point out which tables and
or columns. We knew list of tables from group.
Or it can also used in cases where one or some value in ur DB needs to be updated by new value (because old value was no longer in use...I know different way to handle it with FK relation but if that was not option then..) . You can either search each tables or columns one at a time, or do search on all tables and columns at once. Create Update skripts at once based on your results output and do mass replacements.
----------------------------------------------------------------------------------------
DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'


-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = '
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%',')

WHILE @TableName IS NOT NULL

BEGIN
SET @ColumnName = '
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL

BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ' + @TableName + '.' + @ColumnName + ', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

Thank All