Find a string by searching all tables in SQL Server

If you are like me and have certain restrictions in a production environment, you may wish to use a table variable instead of temp table, and an ad-hoc query rather than a create procedure.

Of course depending on your sql server instance, it must support table variables.

I also added a USE statement to narrow the search scope

USE DATABASE_NAMEDECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))SET NOCOUNT ONDECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)SET  @TableName = ''SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')WHILE @TableName IS NOT NULLBEGIN    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', 'int', 'decimal')                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    ENDSELECT ColumnName, ColumnValue FROM @Results

A bit late but hopefully useful.

Why not try some of the third party tools that can be integrated into SSMS.

I’ve worked with ApexSQL Search (100% free) with good success for both schema and data search and there is also SSMS tools pack that has this feature (not free for SQL 2012 but quite affordable).

Stored procedure above is really great; it’s just that this is way more convenient in my opinion. Also, it would require some slight modifications if you want to search for datetime columns or GUID columns and such…

To update TechDo's answer for SQL server 2012. You need to change: 'FROM ' + @TableName + ' (NOLOCK) ' to FROM ' + @TableName + 'WITH (NOLOCK) ' +

Other wise you will get the following error: Deprecated feature 'Table hint without WITH' is not supported in this version of SQL Server.

Below is the complete updated stored procedure:

CREATE PROC SearchAllTables(@SearchStr nvarchar(100))ASBEGIN    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', 'int', 'decimal')                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName            )            IF @ColumnName IS NOT NULL            BEGIN                INSERT INTO #Results                EXEC                (                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)                     FROM ' + @TableName + 'WITH (NOLOCK) ' +                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2                )            END        END        END    SELECT ColumnName, ColumnValue FROM #ResultsEND