January 3, 2007 at 9:53 am
Hi All,
You may have seen this question before but,, is there a way to query all tables and all columns in a single database for a specific string?
There are around 1000 tables.
Many Thanks
Jeremie
January 3, 2007 at 12:40 pm
Basically what you have to do is use the system tables to list all of the columns from each table. Then dynamically build a string that will list all search columns and then build and ored where condition.
There have been script posted here but I don't have any references to them ATM.
January 3, 2007 at 1:51 pm
Although not something you should be doing a lot this can come in handy:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
* Noel
January 3, 2007 at 2:33 pm
CHEERS FOR THE RESPONSE.. WORKED LIKE A DREAM.
January 3, 2007 at 2:47 pm
Noeld has already posted a great resource to solve your problem while I was working on it. I've got this code now so I might as well post it. This will generate all the selects. FWIW This should be faster than the alternative
DECLARE @searchSQL AS VARCHAR(MAX)
DECLARE @SearchItem AS VARCHAR(MAX) ; SET @SearchItem = '%YOURSTRING%'
SELECT @searchSQL = COALESCE(@searchSQL + '+ CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(MAX)) +
' THEN '' OR '' + IC.Column_Name + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(max))' +
CHAR(13), 'CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(MAX)) +
' THEN IC.Column_Name + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(max))' + CHAR(13))
FROM INFORMATION_SCHEMA.Columns
GROUP BY Ordinal_Position
ORDER BY Ordinal_Position
EXEC('SELECT REPLACE(Query, ''WHERE OR'', ''WHERE'') FROM
(SELECT ''SELECT '''''' + CAST(TABLE_NAME AS VARCHAR(MAX)) + '''''' as tbl, * FROM '' +
CAST(TABLE_NAME AS VARCHAR(MAX)) + '' WHERE '' + ' + @searchSQL + ' AS query
FROM INFORMATION_SCHEMA.Columns IC
WHERE DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
GROUP BY TABLE_NAME) S')
SQL guy and Houston Magician
January 3, 2007 at 2:48 pm
Heh, I guess I'm really slow today. Glad you're sorted Jeremie
SQL guy and Houston Magician
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply