Every now and then I am faced with finding a value in the database, where no one can tell me which field to look in. For instance, if someone has posted stock or created an invoice, then they want to know what all the vouchers are that link to that stock transaction or invoice.

With the following query I am able to pinpoint the tables which are influenced:
(Substitute <Text> with the text you are looking for, and if you know what the column might be replace <Column> with it else remove the text that says <Column>)
DECLARE @texttofind AS VARCHAR(255)
DECLARE @columntofind AS VARCHAR(255)
DECLARE @sql AS VARCHAR(255)
DECLARE @column AS VARCHAR(255)
DECLARE @table AS VARCHAR(255)
DECLARE @maxtablerows AS INTEGER

SET @maxtablerows=5
SET @texttofind=’%<Text>%’
SET @columntofind='<Column>’

DECLARE aCursor CURSOR FOR
SELECT t.name, c.name FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id=c.object_id
INNER JOIN sys.dm_db_partition_stats AS I ON T.object_id = I.object_id AND I.index_id < 2
WHERE i.row_count BETWEEN 1 AND @maxtablerows AND c.name LIKE ‘%’+@columntofind+’%’

OPEN aCursor

FETCH aCursor INTO @table, @column
WHILE @@FETCH_STATUS=0
BEGIN

BEGIN TRY
SET @sql=’BEGIN TRY IF(SELECT COUNT(1) FROM [‘+@table+’] WHERE [‘+@column+’] LIKE ”’+@texttofind+”’)>0 SELECT ”’+@table+’.’+@column+”’ END TRY BEGIN CATCH END CATCH’
EXEC(@sql)
END TRY

BEGIN CATCH
PRINT @table+’ ‘+@column+’ Error Caught’
END CATCH

FETCH aCursor INTO @table, @column
END

CLOSE aCursor
DEALLOCATE aCursor