How to search all text fields in a DB for some substring with T-SQL

Question

I have a huge schema, with several hundreds of tables and several thousands of columns. I'd know that a specific IP address is stored in this database in several places, but I'm not sure what table(s) or column(s) it is stored in. Basically, I'm trying to find everywhere that this IP address is stored in the DB so I can update it to a new value in all those places.

Here's my first crack at a T-SQL statement to print out the table and column name, and the value, for every text column in the database that has the substring 10.15.13 in it.

Now, this works, sort of. The problem is, when I execute it in Management Studio, the call to sp_executesql will actually return all the empty results from every query that returns nothing (i.e. the column doesn't have any records with that substring), and it fills the result window to its max, and then I don't actually see if anything was printed.

Is there a better way to write this query? Or can I run it in some different way so that it only shows me the Tables and Columns where this substring exists?

DECLARE
    @SchemaName VARCHAR(50),
    @TableName VARCHAR(50),
    @ColumnName VARCHAR(50);
BEGIN
    DECLARE textColumns CURSOR FOR
    SELECT s.Name, tab.Name, c.Name
    FROM Sys.Columns c, Sys.Types t, Sys.Tables tab, Sys.Schemas s
    WHERE s.schema_id = tab.schema_id AND tab.object_id = c.object_id AND c.user_type_id = t.user_type_id
    AND t.Name in ('TEXT','NTEXT','VARCHAR','CHAR','NVARCHAR','NCHAR');

    OPEN textColumns

    FETCH NEXT FROM textColumns
    INTO @SchemaName, @TableName, @ColumnName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @sql NVARCHAR(MAX),
                @ParamDef NVARCHAR(MAX),
                @result NVARCHAR(MAX);              
        SET @sql = N'SELECT ' + @ColumnName + ' FROM ' + @SchemaName + '.' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%10.15.13%''';
        SET @ParamDef = N'@resultOut NVARCHAR(MAX) OUTPUT';

        EXEC sp_executesql @sql, @ParamDef, @resultOut = @result OUTPUT;

        PRINT 'Column = ' + @TableName + '.' + @ColumnName + ', Value = ' + @result;
        FETCH NEXT FROM textColumns
        INTO @SchemaName, @TableName, @ColumnName       
    END
    CLOSE textColumns;
    DEALLOCATE textColumns;
END

I'd like to see results something like this where it shows the table/column that the substring was found in, and the full value in that column...

Column = SomeTable.SomeTextColumn, Value = 'https://10.15.13.210/foo'
Column = SomeTable.SomeOtherColumn, Value = '10.15.13.210'

etc.

Aylward

Your close. Compare yours with this example: Searching and finding a string value in all columns in a SQL Server table – Riarial

Awesome Thanks! I actually didn't use that solution from the link because it is for searching a single table at a time, but it had another link to a complete solution for searching all tables in a database; exactly what I needed. vyaskn.tripod.com/search_all_columns_in_all_tables.htm Please post this as an answer and I'll mark it as correct! – Aylward

I've included the answer as well as the link you were referring to for posterity. Thanks! – Riarial

Answer 1

You'r close. Compare yours with this example: Searching and finding a string value in all columns in a SQL Server table

The above link is for searching a single table, however here is another link which includes all tables: How to search all columns of all tables in a database for a keyword?

EDIT : Just in case the link ever goes bad, here's the solution from that link...

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT


    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    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 ('char', 'varchar', 'nchar', 'nvarchar')
                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END 
    END

    SELECT ColumnName, ColumnValue FROM #Results
END


EXEC SearchAllTables '<yourSubstringHere>'

Note: As the comment suggests in the code snippet, it was tested using older versions of SQL Server. This may not work on SQL Server 2012.

Riarial

-1 I get the following error in SQL Server 2012 "Msg 217, Level 16, State 1, Procedure SearchAllTables, Line 54 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)." – Fortaleza

codemonkeh, make sure to add drop storedprocedure and temp table statements after each execution OR before the execution. – Carburetor

get the following error in SQL Server 2012 "Msg 217, Level 16, State 1, Procedure SearchAllTables, Line 54 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). Same error – Silky

@PiotrWolkowski Im getting the same errors 1) how can I reuse this without errors in db listed 2) how can I specify some columns across a couple of tables – Blane

@Blane Perhaps this is a question either to Jeremy (the owner of the answer) or to Jim (the owner of the question) since I only edited this thread. – Chelsae

The error you guys are describing is because there is no batch seperator between the stored procedure definition and the execution statement. Therefore, the execution statement is now part of the stored procedure definition hence it's recursively being executed. Put "GO" between the last END and the Execute statement.. – Autorotation

Needs a fancy trick to add the table row's primary key value to the results, if there IS a primary key. Definitely tricky because could be a single column or a composite key of > 1 columns. – Lope

Answer 2

The chosen answer is brilliant, but I found when using it repeatedly the results were erroneous, so I added some clean up to make it re-runnable with accurate results:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'SearchAllTables')
    DROP PROC SearchAllTables
GO

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Slightly modified by: Natalie Ford, 6/10/15
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    TRUNCATE Table #Results

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    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 ('char', 'varchar', 'nchar', 'nvarchar')
                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END 
    END

    SELECT ColumnName, ColumnValue FROM #Results

    DROP TABLE #Results
END

Sinclare

Tested successfully on SQL 2014 – Nostrum

Answer 3

try this It will not give you the error of limit exceed 32

alter PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN



    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    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 ('char', 'varchar', 'nchar', 'nvarchar')
                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END 
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

Hope it helps

Silky