Screw it, I am going to search all tables, all columns!

Ever had the need to find out where data is stored in your MS SQL Database?
I sometimes do and I find it often faster to simply search every table and every column within these table than to try and hunt down the schema definitions. I get a coffee while the script crunches away.  The @SearchStrColumnName option is there mostly for when you search integers, as you may get too many false positives.

Edit: GitHub Gist works awesome, will have to do that for the previous posts too! Just paste the URL, and wordpress will do the rest, in this case display a fully highlighted T-SQL code.
Edit 4/8/2013 – @SearchStrColumnName wasn’t properly escaped before and this parameter didn’t really work.
Edit 4/9/2013 – Added parameter @FullRowResult, this will cause it to return the full row for each hit. This is usefull when you need to lookup or find related info to the search term.
Edit 4/12/2013 – Added parameter @SearchStrTableName, to limit in what tables we are going to search.
Edit 5/2/2013 – Now also searching in type uniqueidentifier (GUID)
Edit 5/22/2013 – Additional numeric data types will be searched.
Edit 1/7/2014 – Can now also search timestamp value (known as rowversion)
Edit 4/18/2014 – Added a Top parameter that works together with @FullRowResult. This is to help limit the return if a search string is found in a table too often

Advertisements

19 thoughts on “Screw it, I am going to search all tables, all columns!

  1. Just a small hint… if you want to search all Views instead, this is easily possible with this.
    Just change:
    TABLE_TYPE = ‘BASE TABLE’
    into
    TABLE_TYPE = ‘VIEW’

    And BAMMMM!

  2. I had to add “,@FullRowResultRows Bit” behind the @Declare to make it work, beyond that it works like advertised

  3. Msg 207, Level 16, State 1, Line 71
    Invalid column name ‘TableName’.
    Msg 207, Level 16, State 1, Line 71
    Invalid column name ‘ColumnType’.
    Msg 207, Level 16, State 1, Line 70
    Invalid column name ‘TableName’.
    Msg 207, Level 16, State 1, Line 70
    Invalid column name ‘ColumnType’.

    Sorry possibly a dumb question, but what am I doing wrong?? I get the above.

    1. It looks like for some reason you dropped the table #Results?
      Whenever it finds something it put it into the #Results table, and at the end basically does a SELECT from it.

      If you are using the parameter
      SET @FullRowResult = 1
      you can also decide to not even use this temp table since FullRowResult means we echo each found row (limited by @FullRowResultRows)

  4. This is genius. I have been searching and searching, and nothing has worked properly. Your solution is exactly what I need. Thank you so incredibly much!

  5. I just wanted to drop a huge THANK YOU here for this excellent script. You have saved my day searching out a needle in a giant haystack. Great work, thanks for sharing!

  6. Learned a few things from using your query so, for that, thanks!

    Quick question, however; Would it not be more efficient to do the COUNT (*) and GROUP BY statement before inserting, possibly, hundreds of thousands of rows into #Results?
    For my use case, the initial run with your unaltered code took 55 minutes and it returned everything I needed. However, after adding the COUNT and GROUP BY in the SET @sql statement, before inserting into #Results, it took 30 minutes to run.

    Just my 2 cents. Thanks again for the query!

      1. Sure thing!

        My changes:
        –Update the temp table to include a column for COUNT (Line 12)
        CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20),[COUNT] nvarchar(20))

        –Update the @sql statement to COUNT(*) and GROUP BY (Line 46-54)
        SET @sql = ‘SELECT ”’ + @TableName + ”’,”’ + @ColumnName + ”’,’ + CASE @ColumnType WHEN ‘xml’ THEN ‘LEFT(CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX)), 4096),”’
        WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘),”’
        ELSE ‘LEFT(‘ + @ColumnName + ‘, 4096),”’ END + @ColumnType + ”’,COUNT(*)
        FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
        ‘WHERE ‘ + CASE @ColumnType WHEN ‘xml’ THEN ‘CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX))’
        WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘)’
        ELSE @ColumnName END + ‘ LIKE ‘ + @QuotedSearchStrColumnValue + ‘GROUP BY’ + @TableName + ‘.’ + @ColumnName
        INSERT INTO #Results
        EXEC(@sql)

        –Update the SELECT statement at the end of the query (Line 70 & 71)
        SELECT * FROM #Results
        ORDER BY TableName;

        DROP TABLE #Results;

        Let me know if you have any questions.

  7. Here’s the full query I use, with a few tweaks (naming, semi colons, capitalization) here and there:

    DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
    SET @SearchStrColumnValue = ‘ 101’; — Use LIKE syntax
    SET @FullRowResult = 0;
    SET @FullRowResultRows = 3;
    SET @SearchStrTableName = NULL; — NULL for all tables, uses LIKE syntax
    SET @SearchStrColumnName = NULL; — NULL for all columns, uses LIKE syntax
    –SET @SearchStrInXML = 0; — Searching XML data may be slow

    IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL
    BEGIN
    DROP TABLE #Results
    END;

    CREATE TABLE #Results (TABLE_NAME nvarchar(128), COLUMN_NAME nvarchar(128), COLUMN_VALUE nvarchar(max),COLUMN_TYPE nvarchar(20),[COUNT] nvarchar(20), INSPECT_QUERY nvarchar(max))

    SET NOCOUNT ON;

    DECLARE @TableName nvarchar(256) = ”,@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
    SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,””);
    DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

    WHILE @TableName IS NOT NULL
    BEGIN
    SET @TableName = (
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = ‘BASE TABLE’
    AND TABLE_CATALOG = ‘JDE_DEVELOPMENT’
    AND TABLE_SCHEMA = ‘TESTDTA’
    AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
    AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)), ‘IsMSShipped’) = 0
    );
    IF @TableName IS NOT NULL
    BEGIN
    DECLARE @sql VARCHAR(MAX)
    SET @sql = ‘SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = PARSENAME(”’ + @TableName + ”’, 2)
    AND TABLE_NAME = PARSENAME(”’ + @TableName + ”’, 1)
    AND DATA_TYPE = ”nchar”
    AND CHARACTER_MAXIMUM_LENGTH = 12
    –AND DATA_TYPE IN (‘ + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,’%’,”),’_’,”),'[‘,”),’]’,”),’-‘,”)) = 1 THEN ”’tinyint”,”int”,”smallint”,”bigint”,”numeric”,”decimal”,”smallmoney”,”money”,’ ELSE ” END + ”’char”,”varchar”,”nchar”,”nvarchar”,”timestamp”,”uniqueidentifier”’ + CASE @SearchStrInXML WHEN 1 THEN ‘,”xml”’ ELSE ” END + ‘)
    AND COLUMN_NAME LIKE COALESCE(‘ + CASE WHEN @SearchStrColumnName IS NULL THEN ‘NULL’ ELSE ”” + @SearchStrColumnName + ”” END + ‘,COLUMN_NAME)’
    INSERT INTO @ColumnNameTable
    EXEC (@sql)
    WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
    BEGIN
    PRINT @ColumnName
    SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
    SET @sql = ‘SELECT ”’ + @TableName + ”’,”’ + @ColumnName + ”’,’ + CASE @ColumnType WHEN ‘xml’ THEN ‘LEFT(CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX)), 4096),”’
    WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘),”’
    ELSE ‘LEFT(‘ + @ColumnName + ‘, 4096),”’ END + @ColumnType + ”’,COUNT(*),
    ”SELECT * FROM ‘ + @TableName + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ”’ + @QuotedSearchStrColumnValue +”’;”
    FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
    ‘WHERE ‘ + CASE @ColumnType WHEN ‘xml’ THEN ‘CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX))’
    WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘)’
    ELSE @ColumnName END + ‘ LIKE ‘ + @QuotedSearchStrColumnValue + ‘GROUP BY’ + @TableName + ‘.’ + @ColumnName
    INSERT INTO #Results
    EXEC(@sql)
    IF (@@ROWCOUNT > 0 AND @FullRowResult = 1)
    BEGIN
    SET @sql = ‘SELECT TOP ‘ + CAST(@FullRowResultRows AS VARCHAR(3)) + ‘ ”’ + @TableName + ”’ AS [TableFound],”’ + @ColumnName + ”’ AS [ColumnFound],”FullRow>” AS [FullRow>],*’ +
    ‘ FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
    ‘ WHERE ‘ + CASE @ColumnType WHEN ‘xml’ THEN ‘CAST(‘ + @ColumnName + ‘ AS nvarchar(MAX))’
    WHEN ‘timestamp’ THEN ‘master.dbo.fn_varbintohexstr(‘+ @ColumnName + ‘)’
    ELSE @ColumnName END + ‘ LIKE ‘ + @QuotedSearchStrColumnValue
    EXEC(@sql)
    END;
    DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
    END;
    END;
    END;
    SET NOCOUNT OFF;

    SELECT * FROM #Results
    ORDER BY TABLE_NAME;

    DROP TABLE #Results;

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s