MS SQL Server

Search a String or Word From All the Tables in a SQL Server Database

Job History

Objective:

This procedure is to search a string or word from the entire database tables in a given database. Suppose if anyone does not know where exactly the specific string, then this will be your friend. This is tested in SQL 2000.

The technical experts should bear in mind that it may disturb the existing live performance. It is always advisable to take a copy of the database and restore it in different name and search.

CREATE PROC SearchAStringFromAllTablesInADatabase ( @SearchStr nvarchar(100) ) AS BEGIN
--This procedure created to search a string or word from all the tables from the particular database. Create this procedure in the database where you are trying to search.

--Tested on SQL Server 2000
--DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) or if there is an issue you can create like below temp table

--create table #results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

/*
GO
use DBNAME
go
Execute SearchAStringFromAllTablesInADatabase '172064475650'

*/

CREATE TABLE #results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
--	INSERT INTO @Results  This table variable will throw an error like execute cannot be used as a source when inserting into a table variable. Therefore, used the below temp table 
	    INSERT INTO #Results
            EXEC 
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

--SELECT ColumnName, ColumnValue FROM @Results
SELECT ColumnName, ColumnValue FROM #Results
END

 

 

Leave a Reply

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