Searching for Words in All Tables in SQL Server

Hello everyone,

In this article, I will try to share information about searching words in all tables in SQL Server.

In SQL Server, in some cases, we may want to find a word we want in all tables.

You can easily do this using the procedure below.

Note: It may cause performance problems in tables with a large number of records or tables. You can use it in Small and Medium databases.

CREATE   PROC SearchingforWordsinAllTables
(@WordToSearch NVARCHAR(100))
AS
BEGIN

    CREATE TABLE #Sonuc
    (
        ColumnName NVARCHAR(370),
        ColumnValue NVARCHAR(3630)
    );

    SET NOCOUNT ON;

    DECLARE @TableName NVARCHAR(256),
            @ColumnName NVARCHAR(128),
            @Word NVARCHAR(110);

    SET @TableName = N'';
    SET @Word = QUOTENAME('%' + @WordToSearch + '%', '''');

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = N'';
        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','ntext' )
                      AND QUOTENAME(COLUMN_NAME) > @ColumnName
            );
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Sonuc
                EXEC ('SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + 'as varchar(max)), 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @Word);
            END;
        END;
    END;

    SELECT ColumnName,
           ColumnValue
    FROM #Sonuc;

END;

--Use of Procedure

EXEC SearchingforWordsinAllTables  'Dried fruit and bean curd';

Let’s create the procedure and test it.

When you run the procedure, you will see a result like the one above.

The procedure searches ‘CHAR’, ‘VARCHAR’, ‘NCHAR’, ‘NVARCHAR’, ‘NTEXT’ fields.

Good luck to everyone in business and life.