| DECLARE @search_column VARCHAR(255); | |
| DECLARE @search_value VARCHAR(255); | |
| DECLARE @search_type INT; /* 1: specific column 2: string columns 3: numeric columns */ | |
| SET @search_column = 'SEARCHING_COLUMN_NAME'; —wrap with %% for wider search | |
| SET @search_value = 'Any value'; —wrap with %% for wider search | |
| SET @search_type = 1; | |
| DECLARE @containing_tables TABLE | |
| ( | |
| table_name VARCHAR(255) NOT NULL, | |
| column_name VARCHAR(255) NOT NULL, | |
| column_value VARCHAR(255) NOT NULL | |
| ); | |
| DECLARE all_tables_cursor CURSOR FAST_FORWARD for | |
| SELECT | |
| t.name AS 'TableName', c.name AS 'ColumnName' | |
| FROM | |
| sys.columns c JOIN sys.tables t ON c.object_id = t.object_id | |
| WHERE | |
| ((@search_type = 1) AND (UPPER(c.name) LIKE UPPER(@search_column))) /* search into a specific column */ | |
| OR | |
| ((@search_type = 2) AND (c.user_type_id IN (35, 99, 167, 175, 231, 239))) /* search into all string columns */ | |
| OR | |
| ((@search_type = 3) AND (c.user_type_id IN (48,52,56,108,127))) /* search into all numeric columns */ | |
| ORDER BY TableName, ColumnName; | |
| DECLARE @table_name VARCHAR(255), @column_name VARCHAR(255) | |
| OPEN all_tables_cursor | |
| FETCH NEXT FROM all_tables_cursor INTO @table_name, @column_name | |
| WHILE (@@FETCH_STATUS <> –1) | |
| BEGIN | |
| IF (@@FETCH_STATUS <> –2) | |
| BEGIN | |
| BEGIN TRY | |
| DECLARE @select_statement VARCHAR(1000); | |
| SET @select_statement = 'DECLARE all_values_cursor CURSOR FAST_FORWARD FOR SELECT [' + @column_name + '] FROM [' + @table_name + '] WHERE (CONVERT(VARCHAR(255), [' + @column_name + ']) LIKE ' + CHAR(39) + @search_value + CHAR(39) + ');'; | |
| EXEC(@select_statement) | |
| DECLARE @column_value VARCHAR(255) | |
| OPEN all_values_cursor | |
| FETCH NEXT FROM all_values_cursor INTO @column_value | |
| WHILE (@@FETCH_STATUS <> –1) | |
| BEGIN | |
| IF (@@FETCH_STATUS <> –2) | |
| BEGIN | |
| INSERT INTO @containing_tables (table_name, column_name, column_value) VALUES (@table_name, @column_name, @column_value); | |
| END | |
| FETCH NEXT FROM all_values_cursor INTO @column_value | |
| END | |
| CLOSE all_values_cursor; | |
| DEALLOCATE all_values_cursor; | |
| END TRY | |
| BEGIN CATCH | |
| PRINT 'Error reading ' + @table_name + '.' + @column_name + ': ' + ERROR_MESSAGE(); | |
| END CATCH; | |
| END | |
| FETCH NEXT FROM all_tables_cursor INTO @table_name, @column_name | |
| END | |
| CLOSE all_tables_cursor; | |
| DEALLOCATE all_tables_cursor; | |
| —Bonus instruction, you can customize the print below to accomodate specific needs | |
| DECLARE all_selects_cursor CURSOR FAST_FORWARD for | |
| SELECT | |
| table_name, column_name, column_value | |
| FROM | |
| @containing_tables | |
| GROUP BY table_name, column_name, column_value; | |
| OPEN all_selects_cursor | |
| FETCH NEXT FROM all_selects_cursor INTO @table_name, @column_name, @column_value | |
| WHILE (@@FETCH_STATUS <> –1) | |
| BEGIN | |
| IF (@@FETCH_STATUS <> –2) | |
| BEGIN | |
| BEGIN TRY | |
| PRINT 'SELECT * FROM ' + @table_name + ' WHERE (' + @column_name + ' = ' + CHAR(39) + @search_value + CHAR(39) + ');'; | |
| END TRY | |
| BEGIN CATCH | |
| PRINT 'Error reading ' + @table_name + '.' + @column_name + ': ' + ERROR_MESSAGE(); | |
| END CATCH; | |
| END | |
| FETCH NEXT FROM all_selects_cursor INTO @table_name, @column_name, @column_value | |
| END | |
| CLOSE all_selects_cursor; | |
| DEALLOCATE all_selects_cursor; | |
| SELECT table_name, column_name, column_value, count(column_value) AS repeating FROM @containing_tables GROUP BY table_name, column_name, column_value; |
17/Oct/2019
Leave a Reply