SQL Server Trick: Where in the World is That Data Coming From?

Do you ever have a situation where you see data from a database, but have no clue which table or field it is coming from?

Wait no longer….

Here is a script you can use that will tell you in a given database, what table and field contains that information.

For example, to find all occurances of ‘Hello’ in your data:

 EXEC test.dbo.SearchAllTableForText '%Hello%'

Results:

ColumnName                     ColumnValue
[dbo].[People].[FirstName]     ABC Hello
[dbo].[People].[LastName]      Hello DEF

This reports that table ‘People’ and fields ‘FirstName’ and ‘LastName’ contain ‘Hello’

Here is the stored procedure to create it:

-- =============================================
-- Author:  Aydin Akcasu 
-- Create date: 4/12/2015
-- Description: Search all tables and columns for a string.
-- =============================================
CREATE PROCEDURE dbo.SearchAllTableForText
 @SearchStr nvarchar(300) = '%akcasu%'
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
    CREATE TABLE #Results1 (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256);   SET  @TableName = ''
 DECLARE @ColumnName nvarchar(128)

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ( 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
           IF @ColumnName IS NOT NULL
            BEGIN
     INSERT INTO #Results1
       EXEC
       (
        'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
        ' WHERE ' + @ColumnName + ' LIKE ' + ''''+ @SearchStr  + ''''
       )
            IF @@ROWCOUNT > 0
      BEGIN
    --SELECT * FROM #Results1
    INSERT INTO #Results SELECT * FROM #Results1
    TRUNCATE TABLE #Results1
      END
           END
        END  
    END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results1
DROP TABLE #Results

END
-- TEST:
-- EXEC test.dbo.SearchAllTableForText '%Hello%'
GO