Search For A Specific Value or String Throughout An SQL Database

Have you ever wished to have a way of searching for a value or a string throughout your SQL database? I have provided an SQL script that searches a SQL Server database for occurrences of a specific value across all textual and numeric columns in all tables. It uses dynamic SQL and cursors to achieve this. Here’s a step-by-step breakdown:

1. Declare Variables

The script starts by declaring several variables to hold temporary data like table names, column names, the SQL string to be executed dynamically, and the value to search for.

DECLARE @TableName NVARCHAR(256)
DECLARE @ColumnName NVARCHAR(256)
DECLARE @DataType NVARCHAR(256)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @ValueToSearch NVARCHAR(256)
SET @ValueToSearch = '123'  -- Change this value to what you need to search for.        

2. Create a Temporary Table

A temporary table #Results is created to store the results of the search. It holds the table name, column name, and the count of records where the search value was found.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    TableName NVARCHAR(256),
    ColumnName NVARCHAR(256),
    RecordCount INT
)        

3. Cursor to Iterate Over Tables

A cursor named TableCursor is declared to iterate over all tables in the database. It selects each table name from the INFORMATION_SCHEMA.TABLES where the table type is 'BASE TABLE'.

DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'        

4. Open Table Cursor and Fetch Data

The table cursor is opened, and the first table name is fetched into the variable @TableName.

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName        

5. Iterating Over Tables

Within a WHILE loop, the script iterates over each table:

• Column Cursor: For each table, another cursor named ColumnCursor is opened to iterate over columns that are either textual or numeric.
• Fetch Column Information: It fetches each column's name and data type.

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE ColumnCursor CURSOR FOR
    SELECT COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName AND DATA_TYPE IN (...)        

6. Dynamic SQL Construction

For each column, the script constructs and executes a SQL query dynamically based on whether the column is textual or numeric:

• Textual Columns: Uses LIKE for partial matching.
• Numeric Columns: Uses = for exact matching

IF @DataType IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
    SET @SQL = ...
ELSE IF @DataType IN ('int', 'decimal', 'float', 'bigint', 'smallint', 'tinyint', 'money', 'smallmoney', 'numeric', 'real')
    SET @SQL = ...
EXEC sp_executesql @SQL        

7. Handling Errors

The script contains error handling using TRY...CATCH blocks to manage any errors during the execution of dynamic SQL.

BEGIN TRY
    EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
    PRINT 'Error executing: ' + @SQL
    PRINT ERROR_MESSAGE()
END CATCH        

8. Closing Cursors and Displaying Results

After iterating over all columns and tables, the cursors are closed and deallocated. Finally, the results from the #Results table are selected and displayed where the record count is greater than zero.

CLOSE ColumnCursor
DEALLOCATE ColumnCursor
FETCH NEXT FROM TableCursor INTO @TableName
...
CLOSE TableCursor
DEALLOCATE TableCursor
SELECT * FROM #Results WHERE RecordCount > 0        

The Full Code

-- Declare variables
DECLARE @TableName NVARCHAR(256)
DECLARE @ColumnName NVARCHAR(256)
DECLARE @DataType NVARCHAR(256)  -- Declaration for the data type variable
DECLARE @SQL NVARCHAR(MAX)
DECLARE @ValueToSearch NVARCHAR(256)
SET @ValueToSearch = '123'  -- Example numeric value; adjust as needed

-- Create a temporary table to store results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    TableName NVARCHAR(256),
    ColumnName NVARCHAR(256),
    RecordCount INT
)

-- Cursor to iterate over all tables
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName

-- Iterate over each table
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Cursor to iterate over columns of the current table
    DECLARE ColumnCursor CURSOR FOR
    SELECT COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
      AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext', 'int', 'decimal', 'float', 'bigint', 'smallint', 'tinyint', 'money', 'smallmoney', 'numeric', 'real') -- Extended to include numeric types

    OPEN ColumnCursor
    FETCH NEXT FROM ColumnCursor INTO @ColumnName, @DataType

    -- Iterate over each column
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Construct dynamic SQL based on data type
        IF @DataType IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
        BEGIN
            SET @SQL = 'INSERT INTO #Results (TableName, ColumnName, RecordCount) ' +
                       'SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', COUNT(*) ' +
                       'FROM ' + QUOTENAME(@TableName) + ' ' +
                       'WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ''%' + @ValueToSearch + '%'''
        END
        ELSE
        BEGIN
            SET @SQL = 'INSERT INTO #Results (TableName, ColumnName, RecordCount) ' +
                       'SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', COUNT(*) ' +
                       'FROM ' + QUOTENAME(@TableName) + ' ' +
                       'WHERE ' + QUOTENAME(@ColumnName) + ' = ' + @ValueToSearch
        END

        -- Debugging: Print the SQL statement
        PRINT @SQL

        -- Execute the dynamic SQL
        BEGIN TRY
            EXEC sp_executesql @SQL
        END TRY
        BEGIN CATCH
            PRINT 'Error executing: ' + @SQL
            PRINT ERROR_MESSAGE()
        END CATCH

        FETCH NEXT FROM ColumnCursor INTO @ColumnName, @DataType
    END

    -- Close and deallocate column cursor
    CLOSE ColumnCursor
    DEALLOCATE ColumnCursor

    FETCH NEXT FROM TableCursor INTO @TableName
END

-- Close and deallocate table cursor
CLOSE TableCursor
DEALLOCATE TableCursor

-- Display results
SELECT * FROM #Results
WHERE RecordCount > 0        

This script systematically checks each column of each table for the presence of a specified value, records where the value is found, and outputs a summary of where and how many times the value appears.