Find a String Value in the Whole Database: Searching for Text in Tables and Columns

Once in a while you may come across a problem when you are not familiar with the database structure you are dealing with. You may have to deal with the front end only and values that are only presented to you just like they are to the user. If you have no ideas which table to look in or even where to start – it would be nice to simply find tables and columns that contain such text. It certainly gives a start to not only understanding the database design, but also swerving you in the right direction. So, let’s start easy and go right in:

Run this query, you are gonna have fun with it:

DECLARE @Data TABLE
(
      [schema_name] NVARCHAR(50),
      table_name NVARCHAR(50),
      column_name NVARCHAR(50),
      [type_name] NVARCHAR(50)
)

INSERT INTO @Data
      SELECT s.name AS [schema_name], t.name AS table_name, c.name AS column_name, p.name AS [type_name]
            FROM sys.tables t WITH(NOLOCK)
                  INNER JOIN sys.schemas s WITH(NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
                  INNER JOIN sys.columns c WITH(NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
                  INNER JOIN sys.types p WITH(NOLOCK) ON p.system_type_id = c.system_type_id
            WHERE p.name IN ('nvarchar', 'text', 'varchar')
            ORDER BY t.name, c.name;

Now, this query is going to return you every single table with its every single column in the whole database, with columns that contain text. If you are more or less versed in SQL, you’ll get the query. If not – ask a question via a comment – I’ll be sure to answer.

At this point @Data table contains information about every schema, table and column that can be searched for text.
Let’s move on and use sp_executesql to execute dynamic SQL statements built upon what we have just retrieved.
One way to do this is to use a CURSOR:

DECLARE @log NVARCHAR(500)
DECLARE @sql NVARCHAR(MAX)
DECLARE @Count NVARCHAR(50)
DECLARE @i INT
DECLARE @schema_name NVARCHAR(50)
DECLARE @table_name NVARCHAR(50)
DECLARE @column_name NVARCHAR(50)
DECLARE @type_name NVARCHAR(50
DECLARE c CURSOR FOR
      SELECT [schema_name], table_name, column_name, [type_name]
            FROM @Data
OPEN c
FETCH NEXT FROM c INTO @schema_name, @table_name, @column_name, @type_name
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @sql =
            N'SELECT @cnt = COUNT(*)
                  FROM [' + @schema_name + '].[' + @table_name + '] WITH(NOLOCK)'
      EXEC sp_executesql @sql, N'@cnt NVARCHAR(50) OUTPUT', @cnt = @Count OUTPUT
      SET @log = 'Looking in column ' + CAST(@i AS VARCHAR) + ' of ' + CAST(@DataCount AS VARCHAR) + ', ' + @type_name + ' [' + @schema_name + '].[' + @table_name + '].[' + @column_name + '] (' + CAST(@Count AS VARCHAR) + ' records)...'
      RAISERROR(@log, 0, 1) WITH NOWAIT
      SET @sql =
            N'SELECT @cnt = COUNT(*)
                  FROM [' + @schema_name + '].[' + @table_name + '] WITH(NOLOCK)
                  WHERE [' + @column_name + '] LIKE @txt'
      EXEC sp_executesql @sql, N'@cnt NVARCHAR(50) OUTPUT, @txt NVARCHAR(MAX)', @cnt = @Count OUTPUT, @txt = @SearchText
      IF @Count <> '0'
      BEGIN
            INSERT INTO @Result
                  ([schema_name], table_name, column_name, [type_name])
                        VALUES
                  (@schema_name, @table_name, @column_name, @type_name)
      END
      SET @i = @i + 1
      FETCH NEXT FROM c INTO @schema_name, @table_name, @column_name, @type_name
END
CLOSE c
DEALLOCATE c

The sp_executesql built-in stored procedure takes on a sql statement and provided arguments, including output. By calling sp_executesql we are able to execute dynamic sql within the cursor loop to look for a specific value in a table and column that are being dynamically fed to a string query.
We then update the @Result table if we found a match so we can show the matching table, column, etc.

The full SQL code for a query to look for a string in a database’s tables and columns is:

SET NOCOUNT ON

DECLARE @log NVARCHAR(500)

DECLARE @SearchText NVARCHAR(MAX)
SET @SearchText = 'gone searching for this'
DECLARE @Data TABLE
(
      [schema_name] NVARCHAR(50),
      table_name NVARCHAR(50),
      column_name NVARCHAR(50),
      [type_name] NVARCHAR(50)
)

DECLARE @Result TABLE
(
     [schema_name] NVARCHAR(50),
      table_name NVARCHAR(50),
      column_name NVARCHAR(50),
      [type_name] NVARCHAR(50)
)

INSERT INTO @Data
      SELECT s.name AS [schema_name], t.name AS table_name, c.name AS column_name, p.name AS [type_name]
            FROM sys.tables t WITH(NOLOCK)
                  INNER JOIN sys.schemas s WITH(NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
                  INNER JOIN sys.columns c WITH(NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
                  INNER JOIN sys.types p WITH(NOLOCK) ON p.system_type_id = c.system_type_id
            WHERE p.name IN ('nvarchar', 'text', 'varchar')
            ORDER BY t.name, c.name;
   
DECLARE @DataCount INT
SELECT @DataCount = COUNT(*) FROM @Data
SET @log = 'Found ' + CAST(@DataCount AS VARCHAR) + ' columns to search.'
RAISERROR(@log, 0, 1) WITH NOWAIT

DECLARE @sql NVARCHAR(MAX)
DECLARE @Count NVARCHAR(50)
DECLARE @i INT
DECLARE @schema_name NVARCHAR(50)
DECLARE @table_name NVARCHAR(50)
DECLARE @column_name NVARCHAR(50)
DECLARE @type_name NVARCHAR(50)
DECLARE c CURSOR FOR
      SELECT [schema_name], table_name, column_name, [type_name]
            FROM @Data
      
SET @i = 1
OPEN c
FETCH NEXT FROM c INTO @schema_name, @table_name, @column_name, @type_name
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @log = 'Looking in column ' + CAST(@i AS VARCHAR) + ' of ' + CAST(@DataCount AS VARCHAR) + ', ' + @type_name + ' [' + @schema_name + '].[' + @table_name + '].[' + @column_name + '] (' + CAST(@Count AS VARCHAR) + ' records)...'
      RAISERROR(@log, 0, 1) WITH NOWAIT
      SET @sql =
            N'SELECT @cnt = COUNT(*)
                  FROM [' + @schema_name + '].[' + @table_name + '] WITH(NOLOCK)
                  WHERE [' + @column_name + '] LIKE @txt'
      EXEC sp_executesql @sql, N'@cnt NVARCHAR(50) OUTPUT, @txt NVARCHAR(MAX)', @cnt = @Count OUTPUT, @txt = @SearchText
      IF @Count <> '0'
      BEGIN
            INSERT INTO @Result
                  ([schema_name], table_name, column_name, [type_name])
                        VALUES
                  (@schema_name, @table_name, @column_name, @type_name)
      END
      SET @i = @i + 1
      FETCH NEXT FROM c INTO @schema_name, @table_name, @column_name, @type_name
END
CLOSE c
DEALLOCATE c

SELECT * FROM @Result

I hope this helped someone and feel free to use this script and modify it to your needs!

This entry was posted in MSSQL and tagged , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *