The following query should help search for a particular value in the database. This is particularly useful, when you have troubleshoot an issue and you do not any clue where the data might be and do not have to access to launch trace session. This script helped me few times when I inherited a database( with no documentation\guidance) and had to reverse engineer the process with very limited choices.
In the below example, we are searching for value “Toyota”.
CREATE TABLE #output ( schema1 VARCHAR(500),tablename VARCHAR(500),column1 VARCHAR(500),Columnvalue VARCHAR(2000))
SELECT TABLE_NAME,COLUMN_NAME,TABLE_SCHEMA,
'Select top 1 '''+TABLE_SCHEMA+''','''+Table_Name+''','''+Column_Name+''','+quotename(COLUMN_NAME)+' as [ColumnValue] from '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+ '(nolock) where '+quotename(COLUMN_NAME)+' like ''%Toyota%''' AS SQL1
INTO #Test
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Data_Type IN ('char','varchar','text','nchar','nvarchar','ntext')
DECLARE @TABLE_NAME VARCHAR(500)
DECLARE @COLUMN_NAME VARCHAR(500)
DECLARE @TABLE_SCHEMA VARCHAR(500)
DECLARE @SQL1 VARCHAR(max)
DECLARE db_cursor CURSOR FOR
SELECT TABLE_NAME,COLUMN_NAME,TABLE_SCHEMA,SQL1
FROM #test
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME,@TABLE_SCHEMA,@SQL1
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @SQL1
INSERT INTO #output
EXEC (@SQL1)
FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME,@TABLE_SCHEMA,@SQL1
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT * FROM #output
SQL Server Repository 