Find a string by searching all tables in SQL Server

sql

sql-injection

database-optimization

sql-server

byAlex Kataev·Dec 20, 2024

Find a string system-wide in SQL Server by using a dynamic SQL script that loops through table-and-column bounds. This script employs a combination of INFORMATION_SCHEMA and table variables for optimized results, with a focus on text-compatible fields:

-- Terry says "Never leave home without NVARCHAR"
DECLARE @SearchStr nvarchar(100) = N'YourSearchString';

-- Remember kids, tables are your friends!
DECLARE @Results TABLE (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(4000));

-- Terry says "Action without vision is a daydream"
SET NOCOUNT ON;

-- Terry says "Go go gadget cursor!"
DECLARE @Cursor CURSOR FOR
SELECT t.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE' AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext');

-- Let's get this party started!
OPEN @Cursor;

-- Terry says "FETCH, good dog!";
FETCH NEXT FROM @Cursor INTO @TableName, @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- I'm the doctor. I'll take it from here.
    EXEC('INSERT INTO @Results (TableName, ColumnName, ColumnValue)
          SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000) 
          FROM ' + @TableName + ' (NOLOCK)
          WHERE ' + @ColumnName + ' LIKE ''%' + @SearchStr + '%''');

    -- Again, FETCH!
    FETCH NEXT FROM @Cursor INTO @TableName, @ColumnName;
END;

-- Close the party, we're done here.
CLOSE @Cursor;
DEALLOCATE @Cursor;

-- And... it's showtime!
SELECT * FROM @Results;

Substitute 'YourSearchString' with your target string, run the script, and check @Results for your matches.

Digging deeper: Optimizing and securing search

Enhancing performance with focused databases

Trim down your search bounds to specific databases with the USE statement, accelerating the execution and returning results faster.

Safeguarding against SQL injection

Wrap the dynamic SQL elements using the QUOTENAME function, ensuring all user inputs are properly escaped — a safety net against SQL injection attacks.

Optimizing execution with NOLOCK

Using the NOLOCK table hint prevents table-locking during read operations, speeding up the execution while allowing dirty reads. It's the SQL equivalent of a speed run.

Handling large databases smartly

For the big fish — vast databases or long strings — break down your search into smaller, manageable bites, or limit the string length to prevent gargantuan outputs and optimize performance.

Visualising the SQL search

If we imagine the database as a city library, each table would represent a different section. The code navigates through these sections, looking for the target string, much like searching for a specific book in the library.

🏫 Database Library 🏫
| Section (Table) | Status 🕵️‍♂️ |
| ----------------- | --------------- |
| Fiction            | Checked        |
| Non-fiction        | Checked        |
| Guides & Manuals   | Checked        |
| Encyclopedias      | Found!         |

Leverage SQL Server's inherent features

Harnessing system catalog views

System catalog views like sys.objects and sys.schemas let you filter out system related objects and schemas, ensuring the search lands only on user-defined tables. Talk about an informed search!

Adapting the search for data type limitations

Data types matter in a search because, spoiler alert: numeric columns don't store text! Hence, filter columns based on data types to save resources and enhance the search efficiency.

Result precision and review

Make it count! The @Results table variable consolidates your search, painting an accurate picture of where you hit the jackpot.

Advanced tips for SQL power users

Exploring third-party tools

Considering non-programmatic options? Third-party sneaky peeky tools like ApexSQL Search offer GUIs to scan all strings in a SQL Server database.

Checking SQL Server version compatibility

Verify if the version of SQL Server you're using supports table variables and other critical components in the script. Running a compatibility check — a must-have for any great recipe!

Dynamic SQL Execution

Use sp_executesql to create flexible dynamic SQL queries. It's the Swiss army knife of dynamic SQL execution, letting you run input parameters, outputs, and running it all without the risk of SQL injection.

Leaving no room for error

While cursors are the star players here, they take up resources. So remember to CLOSE and DEALLOCATE after the party's over—just like turning off the lights when leaving a room!

explain-codes / Sql / Find a string by searching all tables in SQL Server

Linked

Does the order of where clauses matter in SQL?



Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?



Search for a string in all tables, rows, and columns of a DB



Why use SQL database?



Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

