Script to search in the whole database for columns with a specific value.

SQLServer.sql

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;