Proc to search all tables for a specific piece of data.

For SQL Server 2005 and later.

Not for finding fields but data in those fields.

I can't claim credit for this and I wish I could track down where I found it but it has been very helpful.

Obviously, not performant so of little use in production but handy nonetheless for backwards engineering someone else's undocumented DB.

BEGIN TRAN

declare @search nvarchar ( 100)
set @search = 'My Search String'

-- search all tables for text
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF nullif (object_id ( 'tempdb..#tmpSearch' ), 0 ) IS NOT NULL DROP TABLE #tmpSearch
CREATE TABLE #tmpSearch (
    ListIndex int identity (1, 1),
    CustomSQL nvarchar(2000 )
)
Print 'Getting tables...'
INSERT #tmpSearch (CustomSQL )
select 'IF EXISTS (select * FROM [' + t.TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'') BEGIN PRINT ''Table ' + t.TABLE_NAME + ', Column ' + COLUMN_NAME + ''';select ''' + t. TABLE_NAME + ''' AS TableName , * FROM [' + t.TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @search + '%'' END'  FROM information_schema . columns c JOIN information_schema .tables t on t.Table_name = c.Table_name
where
TABLE_TYPE  = 'BASE TABLE'
and DATA_TYPE IN ('ntext' , 'nvarchar' , 'uniqueidentifier' , 'char' , 'varchar' , 'text')
and t .TABLE_NAME NOT IN ('table_you_dont_want_to_look_in' , 'and_another_one' )

Print 'Searching...

'
declare @index int
declare @customsql nvarchar ( 2000)
WHILE EXISTS (SELECT * FROM #tmpSearch )
BEGIN
    SELECT @index = min( ListIndex) FROM #tmpSearch

    SELECT @customSQL = CustomSQL FROM #tmpSearch WHERE ListIndex = @index

    IF @customSql IS NOT NULL
        EXECUTE (@customSql )

    SET NOCOUNT ON
    DELETE #tmpSearch WHERE ListIndex = @index
    SET NOCOUNT OFF
END

print 'the end.'
ROLLBACK

DROP TABLE #tmpSearch