Search ALL tables query
Ever needed to scan the entire database for a specific value? I ran into problem twice in one week, so I put some thought into it, and hopefully it will help someone else.
DECLARE @wordToSearchFor varchar(50)
SET @wordToSearchFor = 'BizTalk Application Users' -- The word you search for
DECLARE @query varchar(500)
DECLARE SearchAll CURSOR FOR
SELECT 'IF(SELECT COUNT(*) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME
+ '] WHERE ['+COLUMN_NAME+'] = '''+@wordToSearchFor+''')>0
BEGIN SELECT * FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME
+ '] WHERE ['+COLUMN_NAME+'] = '''+@wordToSearchFor+'''
PRINT ''[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'' END'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%CHAR'
OPEN SearchAll
FETCH NEXT FROM SearchAll INTO @query
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@query)
FETCH NEXT FROM SearchAll INTO @query
END
CLOSE SearchAll
DEALLOCATE SearchAll
Hope this helps