17/Oct/2019

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

By Gabriel

	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;

view raw

SQLServer.sql

hosted with ❤ by GitHub

This entry was posted on Thursday, October 17th, 2019 at 8:16 pm and posted in Tips. You can follow any responses to this entry through the RSS 2.0 feed.

Leave a Reply Cancel reply

Enter your comment here...

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

Email (required) (Address never made public)

Name (required)

Website

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

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

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

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

Connecting to %s