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