×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Tobias Richter
Added: Feb 9, 2016 7:42 AM
Modified: Apr 5, 2016 2:55 PM
Views: 69
Tags: sql table select
  1. DECLARE
  2.     @search_string  VARCHAR(100),
  3.     @TABLE_NAME     SYSNAME,
  4.     @table_id       INT,
  5.     @column_name    SYSNAME,
  6.     @sql_string     VARCHAR(2000)
  7.  
  8. SET @search_string = '%MY SEARCH TERM%'
  9.  
  10. DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE TYPE = 'U'
  11.  
  12. OPEN tables_cur
  13.  
  14. FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id
  15.  
  16. WHILE (@@FETCH_STATUS = 0)
  17. BEGIN
  18.     DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
  19.  
  20.     OPEN columns_cur
  21.  
  22.     FETCH NEXT FROM columns_cur INTO @column_name
  23.     WHILE (@@FETCH_STATUS = 0)
  24.     BEGIN
  25.         SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @TABLE_NAME + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @TABLE_NAME + ', ' + @column_name + ''''
  26.  
  27.         EXECUTE(@sql_string)
  28.  
  29.         FETCH NEXT FROM columns_cur INTO @column_name
  30.     END
  31.  
  32.     CLOSE columns_cur
  33.  
  34.     DEALLOCATE columns_cur
  35.  
  36.     FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id
  37. END
  38.  
  39. CLOSE tables_cur
  40.  
  41. DEALLOCATE tables_cur