UmbraCare LOGO

Searching for text across multiple tables in SQL Server

Searching for a specific string across multiple tables in an SQL Server database can be tricky. Here’s a guide on how to make the process easy

Published on: , by:
Piotr Bach picture
Piotr Bach
Wojciech Tengler picture
Wojciech Tengler
MS SQL

Jump to Section

Why Search Text Across SQL Server Tables?

In a complex database, pinpointing where specific data resides can be tricky.

For example, you might need to:

  • Find all instances of a customer’s name across different tables.
  • Audit records that reference a specific keyword.
  • Search unknown or undocumented schemas.

Query to search text in a SQL Server database 

Here’s the heart of our solution - a T-SQL script that scans text columns for a specific string.

The Code:

DECLARE @TargetString VARCHAR(255) = 'Piotr Bach' -- The text string to search for
DECLARE @CurrentSchemaName NVARCHAR(255) = 'dbo' -- The name of the current schema being processed
DECLARE @CurrentTableName NVARCHAR(255)  -- The name of the current table being processed
DECLARE @CurrentColumnName NVARCHAR(255) -- The name of the current column being processed
DECLARE @DynamicSQL NVARCHAR(MAX)        -- Dynamic SQL command to be constructed and executed

-- Cursor to iterate through all relevant tables and columns
DECLARE ColumnCursor CURSOR FOR 
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE DATA_TYPE IN ('varchar', 'char', 'text', 'nvarchar', 'ntext', 'varbinary') -- Include appropriate data types
    AND TABLE_SCHEMA = @CurrentSchemaName

OPEN ColumnCursor

FETCH NEXT FROM ColumnCursor INTO @CurrentSchemaName, @CurrentTableName, @CurrentColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the SQL query for the current column
    SET @DynamicSQL = 'SELECT * FROM [' + @CurrentSchemaName + '].[' + @CurrentTableName + '] WHERE [' + @CurrentColumnName + '] LIKE ''%' + @TargetString + '%'''
 
    -- Execute the dynamic SQL
    EXEC sp_executesql @DynamicSQL

    -- Print the dynamic SQL for debugging purposes
    PRINT('Executed search for "' + @TargetString + '" in schema [' + @CurrentSchemaName + '], table [' + @CurrentTableName + '], column [' + @CurrentColumnName + '].')

    FETCH NEXT FROM ColumnCursor INTO @CurrentSchemaName, @CurrentTableName, @CurrentColumnName
END

CLOSE ColumnCursor
DEALLOCATE ColumnCursor

Key Points

  • Adjust @TargetString for your search term.
  • Modify the DATA_TYPE filter to match your database's column types.
  • Test in a non-production environment to evaluate performance.

How It Works

This script uses three core concepts:

1. Cursors
Iterates through all columns that match the specified data types. While efficient for this use case, cursors can be slower than set-based operations in large datasets.

2. Dynamic SQL
Dynamically builds and executes queries for each column. To avoid SQL injection risks, use parameters (e.g., sp_executesql with parameters) in production scenarios.

3. Data Types
Filter columns are based on their types (e.g., varchar, nvarchar) and are only used to target text-based data.

Benefits and Limitations

Advantages

  • Comprehensive Search: Scans all text-based columns across all tables.
  • Flexible: Easily adaptable for different schemas, tables, or data types.
  • Dynamic: Handles unknown database structures without requiring schema documentation.

Drawbacks

  • Performance Overhead: May be slow on large databases. Consider running during off-peak hours.
  • Cursor Inefficiency: Cursors can impact performance more than set-based operations.
  • SQL Injection Risk: Ensure proper sanitization to secure dynamic queries.

Example Output

Running the script yields results similar to these:

(1 row affected)
Executed search for "Piotr Bach" in schema [dbo], table [umbracoUser], column [userName].

(510 rows affected)
Executed search for "Piotr Bach" in schema [dbo], table [umbracoAudit], column [performingDetails].

(902 rows affected)
Executed search for "Piotr Bach" in schema [dbo], table [umbracoAudit], column [affectedDetails].
Searching for target string via T-SQL in MSSQL database

This output confirms where the search term appears and highlights the affected rows.

Best Practices

  1. Test Before Production: Always run the script in a staging environment first.
  2. Optimize Data Types: Narrow the search to only necessary columns for better performance.
  3. Monitor Impact: Use SQL Profiler or other tools to gauge the performance impact on your database.

Next Steps

💡 Try It Now! Copy and customize the script for your database.

🚀 Have an Idea for Improvement? It's all about collaboration! 

Visit the GitHub repository to share your suggestions or contribute directly. 

🌐 Learn More

Visit our blog for practical SQL tips.

EDITOR'S PICK

↑ Top ↑