How to find tables and columns containing specified column value

Question

In T-SQL is it possible to give me a list of tables and columns that contain specified column value?

For example, I get a employee report generated out of a SQL Server database that shows "John" as name in one of the columns. Now I want to find out where "John" appears as a field value in any table/column in the database.

So in English my query should sound like;

Select table, column 
from database A 
where field = 'John'

Thanks

Answer 1

Try with this procedure

CREATE PROCEDURE FindMyData_String @DataToFind NVARCHAR(4000),
                                   @ExactMatch BIT = 0
AS
    SET NOCOUNT ON

    DECLARE @Temp TABLE
      (
         RowId      INT IDENTITY(1, 1),
         SchemaName SYSNAME,
         TableName  SYSNAME,
         ColumnName SYSNAME,
         DataType   VARCHAR(100),
         DataFound  BIT
      )

    INSERT INTO @Temp
                (TableName,
                 SchemaName,
                 ColumnName,
                 DataType)
    SELECT C.Table_Name,
           C.TABLE_SCHEMA,
           C.Column_Name,
           C.Data_Type
    FROM   Information_Schema.Columns AS C
           INNER JOIN Information_Schema.Tables AS T
                   ON C.Table_Name = T.Table_Name
                      AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE  Table_Type = 'Base Table'
           AND Data_Type IN ( 'ntext', 'text', 'nvarchar', 'nchar',
                              'varchar', 'char' )

    DECLARE @i INT
    DECLARE @MAX INT
    DECLARE @TableName SYSNAME
    DECLARE @ColumnName SYSNAME
    DECLARE @SchemaName SYSNAME
    DECLARE @SQL NVARCHAR(4000)
    DECLARE @PARAMETERS NVARCHAR(4000)
    DECLARE @DataExists BIT
    DECLARE @SQLTemplate NVARCHAR(4000)

    SELECT @SQLTemplate = CASE
                            WHEN @ExactMatch = 1 THEN 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       = '''
                                                      + @DataToFind
                                                      + '''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                            ELSE 'If Exists(Select *
                                          From   ReplaceTableName
                                          Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                       Like ''%'
                                 + @DataToFind
                                 + '%''
                                          )
                                     Set @DataExists = 1
                                 Else
                                     Set @DataExists = 0'
                          END,
           @PARAMETERS = '@DataExists Bit OUTPUT',
           @i = 1

    SELECT @i = 1,
           @MAX = MAX(RowId)
    FROM   @Temp

    WHILE @i <= @MAX
      BEGIN
          SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.'
                                                                          + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
          FROM   @Temp
          WHERE  RowId = @i

          PRINT @SQL

          EXEC SP_EXECUTESQL
            @SQL,
            @PARAMETERS,
            @DataExists = @DataExists OUTPUT

          IF @DataExists = 1
            UPDATE @Temp
            SET    DataFound = 1
            WHERE  RowId = @i

          SET @i = @i + 1
      END

    SELECT SchemaName,
           TableName,
           ColumnName
    FROM   @Temp
    WHERE  DataFound = 1

GO

EXEC FindMyData_String
  @DataToFind='Enter Your String'