Searching for a text using SQL? Oh, that is almost the easiest task we do, right? A simple LIKE operator and an argument with the percentage signs on proper positions and it is done. That is correct, but this is not what I was going to write about.

It is more tricky than the LIKE operator when you do not know which table and column needs to be searched in or you are simply interested in all places in the entire database. In this short article I present a script that searches through all tables in the database looking for a string provided.

Script that finds a string in all tables

The following script iterates through all tables in a database and all varchar, nvarchar, char, nchar, ntext, text columns looking for a text silver. The text is configurable in the first line of the script.

DECLARE @textToFind NVARCHAR(MAX) = 'silver';

DECLARE @textFound BIT;
DECLARE @tableSchema NVARCHAR(255);
DECLARE @tableName NVARCHAR(255);
DECLARE @columnName NVARCHAR(255);
DECLARE tables CURSOR STATIC FOR
       SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
       FROM INFORMATION_SCHEMA.TABLES t
             JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
       WHERE t.TABLE_TYPE = 'BASE TABLE'
         AND c.DATA_TYPE IN('varchar', 'char', 'nvarchar', 'nchar', 'ntext', 'text')
       ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME;

OPEN tables;
WHILE 1=1 BEGIN
       FETCH tables INTO @tableSchema, @tableName, @columnName;
       IF @@FETCH_STATUS <> 0
             BREAK;

       SET @textFound = 0;
       DECLARE @sql NVARCHAR(MAX) = '
       IF EXISTS (SELECT 1
                    FROM ' + QUOTENAME(@tableSchema) + '.' + QUOTENAME(@tableName) + '
                    WHERE CAST(' + QUOTENAME(@columnName) + ' AS NVARCHAR(MAX)) LIKE ''%' + @textToFind + '%'')
             SET @textFound = 1';

       EXEC SP_EXECUTESQL@sql, N'@textFound BIT OUTPUT', @textFound = @textFound OUTPUT;
       IF @textFound = 1 BEGIN
             PRINT 'SELECT * FROM ' + QUOTENAME(@tableSchema) + '.' + QUOTENAME(@tableName)
                   + ' WHERE CAST(' + QUOTENAME(@columnName) + ' AS NVARCHAR(MAX)) LIKE ''%' + @textToFind + '%'';';
       END;
END;

CLOSE tables;
DEALLOCATE tables;
GO

The script returns a list of SELECT statements that can be used to return rows with the provided text. In my case there are two tables where the text was found:

The returned list contains only those queries that really return results. My database has much more tables but the script filtered out those that do not contain the desired text. There are only two tables found: account_groups and accounts.

Now, I have to copy the returned queries into the query editor and execute.

SELECT * FROM [cd].[account_groups] WHERE CAST([agr_name] AS NVARCHAR(MAX)) LIKE '%silver%';
SELECT * FROM [cd].[accounts] WHERE CAST([acc_name] AS NVARCHAR(MAX)) LIKE '%silver%';

Two result sets were returned - one for each table found.