Script has been developed and tested for SQL Server (version 2012)
DECLARE @search_string VARCHAR(100), @TABLE_NAME SYSNAME, @table_id INT, @column_name SYSNAME, @sql_string VARCHAR(2000) SET @search_string = 'FOOBAR' DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE TYPE = 'U' OPEN tables_cur FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239) OPEN columns_cur FETCH NEXT FROM columns_cur INTO @column_name WHILE (@@FETCH_STATUS = 0) BEGIN SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @TABLE_NAME + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @TABLE_NAME + ', ' + @column_name + '''' EXECUTE(@sql_string) FETCH NEXT FROM columns_cur INTO @column_name END CLOSE columns_cur DEALLOCATE columns_cur FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id END CLOSE tables_cur DEALLOCATE tables_cur
Comments
I suppose your script is only for Microsoft SQL Server (doesn't clearly say)?
Other RDBMS's can have similar or way easier solutions.
Yes, sorry, only Microsoft SQL server. I've added it to the blogpost.
I know Oracle, MySQL, ... have easier solutions. Any idea for SQL server? Couldn't find a better one :-( ...
Not that easy in SQL Server idd :(
Best solution would probably be to use a third party tool, like ApexSQL Search (free).
Your script should work, but it will be very slow when running it for a large database.
I did a quick Google search and found you probably got your script from here:
stackoverflow dot com slash questions slash 591853 slash search-for-a-string-in-all-tables-rows-and-columns-of-a-db
The comments in the answer also say it will be "RIDICULOUSLY slow" :)
The answer below that also suggests ApexSQL, or use a script with temporary tables instead of cursors, which will increase the speed significantly.
Add new comment