sqlsql-servert-sqlsearch

Search all tables in all databases on server for a string


Edit: This question was flagged as a duplicate, but it is not. The other answers on SO show how to search all tables in a single database, I need to search all tables in EVERY database on a given server.

I need to search all tables for all databases on a server for a search string. I've got email address littered throughout tables that are going to have a change of domain and I need to prepare a report that shows where these email addresses are located. I am not going to be able to add a stored procedure to all the databases so I need a query to do this that's doesn't involve exec-ing a sp repeatedly. I pulled this code off the net and was using it to search all tables but I haven't been able to figure out how to run it on all databases.

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

SET NOCOUNT ON

DECLARE @SearchStr nvarchar(100), @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @SearchStr = '@domaintobereplaced.com'
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 top 10 ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM #Results

Solution

  • This question is a bit old at this point and most of the answers seem to miss the mark in terms of searching ALL servers and ALL databases on each server. Turned out my manager was just trying to give me an impossible and time consuming task aka busy work to stall me because our team was getting disbanded in a re-org and we were all being sent to different parts in the company.

    So, for the sake of writing a comprehensive answer I will say my approach now would be to create a console application that accepts a comma separated list of server names. It would loop through all the server names and connect to them one by one. Once in a server I would query for and loop through all the user created databases and run the search all tables query on each one. If results were found I would write them to a file stored in a directory with a [ServerName]_[DatabaseName]_Results title and then use the outputted list of files to review and develop and action plan for further querying/remediation. It doesn't appear from the answers above that there is a way to do this in SQL Server.