SQL Server script to scan the whole database looking for a specific value in a specific column

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 @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;

view raw
SQLServer.sql
hosted with ❤ by GitHub


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s