Find a string by searching all tables in SQL Server

Question

Is there any way to search for a string in all tables of a database in SQL Server?

I want to search for string say john. The result should show the tables and their respective row that contain john.

possible duplicate of [search all tables, all columns for a specific value SQL Server](http://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server) — Mahmoud Gamal, Apr 02 '13 at 06:14
possible duplicate of [How do I find a value anywhere in a SQL Server Database?](http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database) — Jonathan Leffler, Apr 09 '13 at 05:16

score 392 · Accepted Answer · answered Dec 08 '14 at 15:17

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_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (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 + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

Awesome. I added "PRINT @TableName + '.' + @ColumnName" on line 53, so I could get some status as it worked through my large database. But this is very helpful. — ProVega, Jun 05 '15 at 23:43
Thank you! made searching an old ACT! CRM database easier, where the fieldon screen was "Company reg" and the database field was "tickersymbol" however the hell that came about! lol — Liam Wheldon, Nov 27 '19 at 13:04
If I could upvote every time I come here to copy this mighty query, you'd easily have twice the score :D — DurkoMatko, Aug 13 '20 at 08:10
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT' — Peter Kionga-Kamau, Dec 20 '20 at 18:56
I'm getting a 2-line error that repeats around a dozen times: `Msg 102, Level 15, State 1, Line 1` `Incorrect syntax near 'Data$'.` Any idea how to debug or fix that? — Peak Jason, Sep 13 '21 at 21:03
My two cents. Ran both the stored procedure approach (https://stackoverflow.com/a/19846145/2579638) and this script one after the other. to observe any time difference. This script took approx. 8 minutes to finish on my particular database. Stored proc was slightly faster and took only 5 min 14 sec. Not a huge difference but for much larger databases it could be an issue. My db is MS SQL Server 2016 and size of 16099.94 MB — U A, Sep 26 '21 at 15:53

score 49 · Answer 2 · answered Oct 01 '13 at 09:28

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…

Apex SQL Search isn't free as of 2021. – Alan B Aug 11 '21 at 13:46 — Alan B, Aug 11 '21 at 13:46

score 47 · Answer 3 · edited May 23 '17 at 12:02

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)
)
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', '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 #Results
END

@DanielL.VanDenBosch You save this as a stored procedure and then you execute the stored procedure. — TylerH, Jul 20 '22 at 13:53

score 35 · Answer 4 · edited Nov 02 '17 at 11:17

A bit late, but you can easily find a string with this query

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE  type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id 
        AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] 
            LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

            EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
        END

    CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur

This script fails when the schema of a table is not "dbo" unfortunately most of the tables in be DB have different schemas. Works fine for the couple of "dbo" ones. — rob, Nov 02 '17 at 11:12
Just edited the initial cursor to INNER JOIN on the schemas table so now it does have the schema in the table name when it comes to use it. — rob, Nov 02 '17 at 11:18
I also added 99 (ntext) to the list of system_types to search as currently it ignores all the xml fields. ` AND system_type_id IN (167, 175, 231, 239, 99)` — rob, Nov 02 '17 at 13:53
Err... this script just finds column names, not the content in rows. Isn't that what the OP was asking for? — Dan H., Mar 10 '23 at 20:38

sbrbot · Answer 5 · 2020-05-14T13:28:04.470

There's no need for nested looping (outer looping through tables and inner looping through all table columns). One can retrieve all (or arbitrary selected/filtered) table-column combinations from INFORMATION_SCHEMA.COLUMNS and in one loop simply pass through (search) all of them:

DECLARE @search VARCHAR(100), @table SYSNAME, @column SYSNAME

DECLARE curTabCol CURSOR FOR
    SELECT c.TABLE_SCHEMA + '.' + c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t 
      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
    WHERE c.DATA_TYPE IN ('varchar','nvarchar') -- searching only in these column types
    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names

SET @search='john'

OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @table, @column

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXECUTE('IF EXISTS 
             (SELECT * FROM ' + @table + ' WHERE ' + @column + ' = ''' + @search + ''') 
             PRINT ''' + @table + '.' + @column + '''')
    FETCH NEXT FROM curTabCol INTO @table, @column
END

CLOSE curTabCol
DEALLOCATE curTabCol

score 5 · Answer 6 · answered Mar 10 '17 at 08:50

I have written a SP for the this which returns the search results in form of Table name, the Column names in which the search keyword string was found as well as the searches the corresponding rows as shown in below screen shot.

This might not be the most efficient solution but you can always modify and use it according to your need.

IF OBJECT_ID('sp_KeywordSearch', 'P') IS NOT NULL
    DROP PROC sp_KeywordSearch
GO

CREATE PROCEDURE sp_KeywordSearch @KeyWord NVARCHAR(100)
AS
BEGIN
    DECLARE @Result TABLE
        (TableName NVARCHAR(300),
         ColumnName NVARCHAR(MAX))

    DECLARE @Sql NVARCHAR(MAX),
        @TableName NVARCHAR(300),
        @ColumnName NVARCHAR(300),
        @Count INT

    DECLARE @tableCursor CURSOR

    SET @tableCursor = CURSOR LOCAL SCROLL FOR
    SELECT  N'SELECT @Count = COUNT(1) FROM [dbo].[' + T.TABLE_NAME + '] WITH (NOLOCK) WHERE CAST([' + C.COLUMN_NAME +
            '] AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + N'%''',
            T.TABLE_NAME,
            C.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.TABLES AS T WITH (NOLOCK)
    INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C WITH (NOLOCK)
    ON      T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
            T.TABLE_NAME = C.TABLE_NAME
    WHERE   T.TABLE_TYPE = 'BASE TABLE' AND
            C.TABLE_SCHEMA = 'dbo' AND
            C.DATA_TYPE NOT IN ('image', 'timestamp')

    OPEN @tableCursor
    FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @Count = 0

        EXEC sys.sp_executesql
            @Sql,
            N'@Count INT OUTPUT',
            @Count OUTPUT

        IF @Count > 0
        BEGIN
            INSERT  INTO @Result
                    (TableName, ColumnName)
            VALUES  (@TableName, @ColumnName)
        END

        FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
    END

    CLOSE @tableCursor
    DEALLOCATE @tableCursor

    SET @tableCursor = CURSOR LOCAL SCROLL FOR
    SELECT  SUBSTRING(TB.Sql, 1, LEN(TB.Sql) - 3) AS Sql, TB.TableName, SUBSTRING(TB.Columns, 1, LEN(TB.Columns) - 1) AS Columns
    FROM    (SELECT R.TableName, (SELECT R2.ColumnName + ', ' FROM @Result AS R2 WHERE R.TableName = R2.TableName FOR XML PATH('')) AS Columns,
                    'SELECT * FROM ' + R.TableName + ' WITH (NOLOCK) WHERE ' +
                    (SELECT 'CAST(' + R2.ColumnName + ' AS NVARCHAR(MAX)) LIKE ''%' + @KeyWord + '%'' OR '
                     FROM   @Result AS R2
                     WHERE  R.TableName = R2.TableName
                    FOR
                     XML PATH('')) AS Sql
             FROM   @Result AS R
             GROUP BY R.TableName) TB
    ORDER BY TB.Sql

    OPEN @tableCursor
    FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT @Sql
        SELECT  @TableName AS [Table],
                @ColumnName AS Columns
        EXEC(@Sql)

        FETCH NEXT FROM @tableCursor INTO @Sql, @TableName, @ColumnName
    END

    CLOSE @tableCursor
    DEALLOCATE @tableCursor

END

Hi @DupinderSingh. Invoke the stored procedure this way: `EXEC sp_KeywordSearch '2022-OH'`, where the search string is `2022-OH`. — Jaime Montoya, Jun 14 '23 at 15:05

score 2 · Answer 7 · answered Nov 13 '20 at 08:46

Improving the amazing answer from @Brandon, I added type to ntext and xml using castings:

BEGIN TRAN

DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (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', 'ntext', 'xml')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT((cast(' + @ColumnName + ' as nvarchar(max))), 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE (cast(' + @ColumnName + ' as nvarchar(max))) LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

ROLLBACK

score 2 · Answer 8 · answered Feb 02 '22 at 05:23

Not to take away from the excellent answer by @Brandon but the OP requested "The result should show the tables and their respective row". I created a record-level solution based on the original answer from @Brandon that also avoids the use of CURSORs used by others here by simply concatenating all the appropriate columns into one string. (I have the feeling this would perform better but I honestly haven't compared results - feel free to do so and provide feedback here.)

NB I have used the STRING_AGG function to concatenate the columns. If you have an older version of SQL Server you may need to use one of the alternate methods that can be found online.

CREATE PROC [dbo].[SearchAllTablesByRecord]
(
    @SearchStr NVARCHAR(100)
)
AS
BEGIN            

    SET NOCOUNT ON

    DECLARE @TableName NVARCHAR(256), @SearchStr2 NVARCHAR(110)
    DECLARE @ColumnList NVARCHAR(2048), @ColumnJoin NVARCHAR(2048)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    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     QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                            AND     OBJECTPROPERTY(
                                            OBJECT_ID(
                                                    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                                                     ), 'IsMSShipped'
                                                   ) = 0
            )
            
            PRINT @TableName    -- For progress monitoring
            SET @ColumnList = 
            (
                SELECT STRING_AGG(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')
            )
            SET @ColumnJoin = REPLACE(@ColumnList,',','+')
            
            IF @ColumnList <> '' 
                EXEC
                (
                        'SELECT ''' + @TableName + ''' AS [Table],' + @ColumnList + '
                        INTO #Results
                        FROM ' + @TableName + ' (NOLOCK) ' +
                        ' WHERE ' + @ColumnJoin + ' LIKE ' + @SearchStr2 + '
                        
                        IF @@ROWCOUNT > 0 SELECT * FROM #Results'
                )
    END
END

Paul M · Answer 9 · 2022-05-25T20:29:24.953

There are lots of workable answers already. Just thought I would add one I came up with that has a lot of optional funcionality.

--=======================================================================
--  MSSQL Unified Search
--  Minimum compatibility level = 130 (SQL Server 2016)
--      NOTE: The minimum compatibility level is required by the built-in STRING_SPLIT() function.
--          However, you can create the STRING_SPLIT() function at the bottom of this script for
--          lower versions of MSSQL Server.
--
--  Usage:
--      Set the parameters below and execute this script.
--
/************************ Enter Parameters Here ************************/
/**/
/**/    DECLARE @SearchString VARCHAR(1000) = 'string to search for';  -- Accepts SQL wilcards
/**/
/**/    DECLARE @IncludeUserTables BIT = 1;
/**/    DECLARE @IncludeViews BIT = 0;
/**/    DECLARE @IncludeStoredProcedures BIT = 0;
/**/    DECLARE @IncludeFunctions BIT = 0;
/**/    DECLARE @IncludeTriggers BIT = 0;
/**/
/**/    DECLARE @DebugMode BIT = 0;
/**/    DECLARE @ExcludeColumnTypes NVARCHAR(500) = 'text, ntext, char, nchar, timestamp, bigint, tinyint, smallint, bit, date, time, smalldatetime, datetime, datetime2, real, money, float, decimal, binary, varbinary, image';  -- Comma delimited list
/**/
/***********************************************************************/


SET NOCOUNT ON;
SET @SearchString = QUOTENAME(@SearchString,'''');

DECLARE @Results TABLE ([ObjectType] NVARCHAR(200), [ObjectName] NVARCHAR(200), [ColumnName] NVARCHAR(400), [Value] NVARCHAR(MAX), [SelectStatement] NVARCHAR(1000));
DECLARE @ExcludeColTypes TABLE (system_type_id INT);

INSERT INTO @ExcludeColTypes ([system_type_id])
    SELECT [system_type_id]
    FROM sys.types WHERE
    [name] IN (
        SELECT LTRIM(RTRIM([value])) FROM STRING_SPLIT(@ExcludeColumnTypes,',')
        );

DECLARE @ObjectType NVARCHAR(200);
DECLARE @ObjectName NVARCHAR(200);
DECLARE @Value NVARCHAR(MAX);
DECLARE @SelectStatement NVARCHAR(1000);
DECLARE @Query NVARCHAR(4000);


/********************* Table Objects *********************/
IF (@IncludeUserTables = 1)
BEGIN
    DECLARE @TableObjectId INT = (SELECT MIN([object_id]) FROM sys.tables);
    DECLARE @ColumnId INT;
    WHILE @TableObjectId IS NOT NULL
    BEGIN
    
        SELECT @ObjectType = 'USER TABLE';
        SELECT @ObjectName = '[' + SCHEMA_NAME([schema_id]) + '].[' + OBJECT_NAME(@TableObjectId) + ']' FROM sys.tables WHERE [object_id] = @TableObjectId;

        SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId);
        WHILE @ColumnId IS NOT NULL
        BEGIN

            SELECT @Value = '[' + [name] +']' FROM sys.columns WHERE [object_id] = @TableObjectId AND column_id = @ColumnId;

            SET @SelectStatement = 'SELECT * FROM ' + @ObjectName + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';

            SET @Query = 'SELECT '
                + QUOTENAME(@ObjectType, '''')
                + ', ' + QUOTENAME(@ObjectName, '''')
                + ', ' + QUOTENAME(@Value, '''')
                + ', ' + @Value
                + ', ''' + REPLACE(@SelectStatement,'''','''''') + ''''
                + ' FROM ' + @ObjectName
                + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';

            IF @DebugMode = 0
            BEGIN
                INSERT INTO @Results EXEC(@Query);
            END;
            ELSE
            BEGIN
                PRINT 'Select Statement:  ' + @SelectStatement;
                PRINT 'Query:  ' + @Query;
            END;

            SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId AND [column_id] > @ColumnId);
        END;

        SET @TableObjectId = (SELECT MIN([object_id]) FROM sys.tables WHERE [object_id] > @TableObjectId);
    END;
END;

/********************* Objects Other than Tables *********************/
SET @Query = 'SELECT ' +
    'ObjectType = CASE ' +
        'WHEN b.[type] = ''V'' THEN ''VIEW'' ' +
        'WHEN b.[type] = ''P'' THEN ''STORED PROCEDURE'' ' +
        'WHEN b.[type] = ''FN'' THEN ''SCALAR-VALUED FUNCTION'' ' +
        'WHEN b.[type] = ''IF'' THEN ''TABLE-VALUED FUNCTION'' ' +
        'WHEN b.[type] = ''TR'' THEN ''TRIGGER'' ' +
    'END ' +
    ',[ObjectName] = ''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'' ' +
    ',[ColumnName] = NULL ' +
    ',[Value] = a.[definition] ' +
    ',[SelectStatement] = ''SP_HELPTEXT '' + QUOTENAME(''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'','''''''') + '';'' ' +
'FROM [sys].[sql_modules] a ' +
'JOIN [sys].[objects] b ON a.[object_id] = b.[object_id] ' +
'WHERE ' +
    '( ' +
    '   a.[definition] LIKE ' + @SearchString + 
    ') ' +
    'AND ' +
    '( ' +
    '   ( ' +
            CAST(@IncludeViews AS VARCHAR(1)) + ' = 1 ' +
    '       AND ' +
    '       b.[type] IN (''V'') ' +
    '   ) ' +
    '   OR ' +
    '   ( ' +
            CAST(@IncludeStoredProcedures AS VARCHAR(1)) + ' = 1 ' +
    '       AND ' +
    '       b.[type] IN (''P'') ' +
    '   ) ' +
    '   OR ' +
    '   ( ' +
            CAST(@IncludeFunctions AS VARCHAR(1)) + ' = 1 ' +
    '       AND ' +
    '       b.[type] IN (''FN'',''IF'') ' +
    '   ) ' +
    '   OR ' +
    '   ( ' +
            CAST(@IncludeTriggers AS VARCHAR(1)) + ' = 1 ' +
    '       AND ' +
    '       b.[type] IN (''TR'') ' +
    '   ) ' +
    '); ';

IF @DebugMode = 0
BEGIN
    INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
    PRINT 'Select Statement:  ' + @SelectStatement;
    PRINT 'Query:  ' + @Query;
END;

IF @DebugMode = 0
BEGIN
    SELECT 
        [ObjectType]
        ,[ObjectName]
        ,[ColumnName]
        ,[Value]
        ,[Count] = CASE
            WHEN [ObjectType] IN ('USER TABLE') THEN COUNT(1)
            ELSE NULL
        END
        ,[SelectStatement]
    FROM @Results
    GROUP BY [ObjectType], [ObjectName], [ColumnName], [Value], [SelectStatement]
    ORDER BY [Value];
END;

/********************** STRING_SPLIT() FUNCTION **********************    
CREATE FUNCTION STRING_SPLIT (
    @Expression nvarchar(4000)
    ,@Delimiter nvarchar(100)
)
RETURNS @Ret TABLE ([value] NVARCHAR(4000))
AS
BEGIN

    DECLARE @Start INT = 0, @End INT, @Length INT;
    SELECT @End = CHARINDEX(@Delimiter,@Expression), @Length = @End - @Start;

    IF @End <= 0
    BEGIN
        INSERT INTO @Ret ([value]) VALUES (@Expression);
    END
    ELSE
    BEGIN
        WHILE @Length >= 0
        BEGIN
            INSERT INTO @Ret ([value])
                SELECT ltrim(rtrim(substring(@Expression,@Start,@Length)));
    
            SELECT @Start = @End + LEN(@Delimiter)
            SELECT @End = CHARINDEX(@Delimiter,@Expression,@Start)
            IF @End < 1
                SELECT @End = LEN(@Expression) + 1;
            SELECT @Length = @End - @Start;
    
        END;
    END;
    RETURN;
END;

*********************************************************************/

I'm wondering, why did you `text, ntext, char and nchar`? All of them are for text. I still believe it's the best solution overall. Thanks, @Paul. — Federico Navarrete, Oct 24 '22 at 08:46
@FedericoNavarrete SQL Server does not treat them all the same. While they all might be "text" to a human, the DBMS differentiates them. The script is based on SQL Server Data Types. Not how a human interprets them. — Paul M, Feb 17 '23 at 15:50

score 0 · Answer 10 · answered Sep 05 '18 at 20:41

This was very helpful. I wanted to import this function to a Postgre SQL database. Thought i would share it with anyone who is interested. Will have them a few hours. Note: this function creates a list of SQL statements that can be copied and executed on the Postgre database. Maybe someone smarter then me can get Postgre to create and execute the statements all in one function.

CREATE OR REPLACE FUNCTION SearchAllTables(_search text) RETURNS TABLE( txt text ) as $funct$
    DECLARE __COUNT int;
    __SQL text;
BEGIN
    EXECUTE 'SELECT COUNT(0) FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE    DATA_TYPE = ''text'' 
                    AND          table_schema = ''public'' ' INTO __COUNT;

    RETURN QUERY 
        SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY table_name) < __COUNT THEN 
            'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "'  || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search  || '%'' UNION ALL' 
            ELSE 
            'SELECT ''' || table_name ||'.'|| column_name || ''' AS tbl, "'  || column_name || '" AS col FROM "public"."' || "table_name" || '" WHERE "'|| "column_name" || '" ILIKE ''%' || _search  || '%'''
        END AS txt

                    FROM     INFORMATION_SCHEMA.COLUMNS
                    WHERE    DATA_TYPE = 'text' 
                    AND          table_schema = 'public';
END
$funct$ LANGUAGE plpgsql;

score 0 · Answer 11 · answered Sep 11 '19 at 08:07

The answer that was mentioned in this post already several times I have adopted a little bit because I needed to search in only one table too:

(and also made input for the table name a bit more simpler)

ALTER PROC dbo.db_compare_SearchAllTables_sp
(
    @SearchStr nvarchar(100),
    @TableName nvarchar(256) = ''
)
AS
BEGIN
        if PARSENAME(@TableName, 2) is null
            set @TableName = 'dbo.' + QUOTENAME(@TableName, '"')

    declare @results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @ColumnName nvarchar(128) = '', @SearchStr2 nvarchar(110)
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
        IF @TableName <> ''
        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 + ' WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
        ELSE
    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 + ' WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM @results
END

score 0 · Answer 12 · answered Sep 30 '21 at 10:20

[CREATE 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
    
    EXEC SearchAllTables 'Computer'][1]


  [1]: https://koukia.ca/search-for-a-string-in-all-tables-and-all-fields-of-a-database-with-tsql-b6f8a1bfda37

score 0 · Answer 13 · answered Mar 17 '23 at 14:47

This script allows searching through all occurrences of all fields in all tables within a database, regardless of the data type!

Indeed, if it is a field with a numeric data type, the TRY_CAST function attempts to convert the expression into the correct data type. If the conversion succeeds, it returns the converted value. If the conversion fails, it returns NULL instead of raising an exception.

This is very convenient when you are unsure of the data type or want to use the same script for any search.

    DECLARE @searchValue NVARCHAR(255) = '1856' -- Text or Number (text is converted in number if necessary)
    
    IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL
    BEGIN
        DROP TABLE #SearchResults
    END
    
    CREATE TABLE #SearchResults (
        TableName NVARCHAR(128),
        ColumnName NVARCHAR(128),
        Value NVARCHAR(MAX)
    )
    
    DECLARE @tableName NVARCHAR(128)
    DECLARE @columnName NVARCHAR(128)
    DECLARE @query NVARCHAR(MAX)
    DECLARE @type NVARCHAR(50)
    
    DECLARE db_cursor CURSOR FOR
    SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS Type
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    JOIN sys.types ty ON c.user_type_id = ty.user_type_id
    WHERE ty.name IN ('nvarchar', 'varchar', 'int', 'bigint', 'smallint', 'tinyint')
    
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @tableName, @columnName, @type
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @type IN ('nvarchar', 'varchar')
        BEGIN
            SET @query = 'INSERT INTO #SearchResults (TableName, ColumnName, Value)
                          SELECT DISTINCT ''' + @tableName + ''', ''' + @columnName + ''', ' + 'CONVERT(NVARCHAR(MAX), ' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' LIKE ''%' + @searchValue + '%'''
        END
        ELSE IF @type IN ('int', 'bigint', 'smallint', 'tinyint')
        BEGIN
            SET @query = 'INSERT INTO #SearchResults (TableName, ColumnName, Value)
                          SELECT DISTINCT ''' + @tableName + ''', ''' + @columnName + ''', ' + 'CONVERT(NVARCHAR(MAX), ' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' = TRY_CAST(' + QUOTENAME(@searchValue, '''') + ' AS ' + @type + ')'
        END
    
        EXEC sp_executesql @query
    
        FETCH NEXT FROM db_cursor INTO @tableName, @columnName, @type
    END
    
    CLOSE db_cursor
    DEALLOCATE db_cursor
    
    SELECT * FROM #SearchResults
    
    DROP TABLE #SearchResults

Find a string by searching all tables in SQL Server

13 Answers13

Linked

Related