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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy