SQL Server Mentalist

Hi Folks,

In this article we are going to cover How to Search for a string in all tables of SQL Server Database.

In this post we are going to discuss following points:

• Problem Statement of SQL Script:
• Description of SQL Script
• Input Parameter of SQL Script
• SQL Script Code
• SQL Script Output Screenshot
• User Level to execute

Problem Statement of SQL Script:

Search for a string in all tables of SQL Server Database.

Description of SQL Script:

This Script search through all tables or given Table for a given string.

Input Parameter of SQL Script

@Tablenames — Provide a single table name or multiple table names with comma separated.

If left blank, it will check for all the tables in the database

@SearchStr — Provide the search string. Use the ‘%’ to coin the search.

EX: X%— will give data staring with X

@GenerateSQLOnly — Provide 1 if you only want to generate the SQL statements without seraching the database.

By default it is 0 and it will search.

SQL Script Code

IF OBJECT_ID('SearchTables', 'P') IS NOT NULL
    DROP PROCEDURE SearchTables
GO

CREATE PROCEDURE SearchTables @Tablenames VARCHAR(500)
    ,@SearchStr NVARCHAR(60)
    ,@GenerateSQLOnly BIT = 0
AS
SET NOCOUNT ON

DECLARE @CheckTableNames TABLE (Tablename SYSNAME)
DECLARE @SQLTbl TABLE (
    Tablename SYSNAME
    ,WHEREClause VARCHAR(MAX)
    ,SQLStatement VARCHAR(MAX)
    ,Execstatus BIT
    )
DECLARE @sql VARCHAR(MAX)
DECLARE @tmpTblname SYSNAME

IF LTRIM(RTRIM(@Tablenames)) IN (
        ''
        ,'%'
        )
BEGIN
    INSERT INTO @CheckTableNames
    SELECT NAME
    FROM sys.tables
END
ELSE
BEGIN
    SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames, ',', 
            ''' UNION SELECT ''') + ''''

    INSERT INTO @CheckTableNames
    EXEC (@sql)
END

INSERT INTO @SQLTbl (
    Tablename
    ,WHEREClause
    )
SELECT SCh.NAME + '.' + ST.NAME
    ,(
        SELECT '[' + SC.NAME + ']' + ' LIKE ''' + @SearchStr + ''' OR ' 
            + CHAR(10)
        FROM SYS.columns SC
        JOIN SYS.types STy ON STy.system_type_id = SC.
            system_type_id
            AND STy.user_type_id = SC.user_type_id
        WHERE STY.NAME IN (
                'varchar'
                ,'char'
                ,'nvarchar'
                ,'nchar'
                )
            AND SC.object_id = ST.object_id
        ORDER BY SC.NAME
        FOR XML PATH('')
        )
FROM SYS.tables ST
JOIN @CheckTableNames chktbls ON chktbls.Tablename = ST.NAME
JOIN SYS.schemas SCh ON ST.schema_id = SCh.schema_id
WHERE ST.NAME <> 'SearchTMP'
GROUP BY ST.object_id
    ,SCh.NAME + '.' + ST.NAME;

UPDATE @SQLTbl
SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + 
    ' WHERE ' + substring(WHEREClause, 1, len(WHEREClause) - 5)

DELETE
FROM @SQLTbl
WHERE WHEREClause IS NULL

WHILE EXISTS (
        SELECT 1
        FROM @SQLTbl
        WHERE ISNULL(Execstatus, 0) = 0
        )
BEGIN
    SELECT TOP 1 @tmpTblname = Tablename
        ,@sql = SQLStatement
    FROM @SQLTbl
    WHERE ISNULL(Execstatus, 0) = 0

    IF @GenerateSQLOnly = 0
    BEGIN
        IF OBJECT_ID('SearchTMP', 'U') IS NOT NULL
            DROP TABLE SearchTMP

        EXEC (@SQL)

        IF EXISTS (
                SELECT 1
                FROM SearchTMP
                )
        BEGIN
            SELECT Tablename = @tmpTblname
                ,*
            FROM SearchTMP
        END
    END
    ELSE
    BEGIN
        PRINT REPLICATE('-', 100)
        PRINT @tmpTblname
        PRINT REPLICATE('-', 100)
        PRINT replace(@sql, 'INTO SearchTMP', '')
    END

    UPDATE @SQLTbl
    SET Execstatus = 1
    WHERE Tablename = @tmpTblname
END

SET NOCOUNT OFF
GO

SQL Script Output Screenshot

User Level to execute

200

Hope you will like to How to Search for a string in all tables of SQL Server Database.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Infographic Mentalist >> Image worth explaining thousand Words

Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

DBA Mentalist >>Advance SQL Server Blog

Microsoft BI Mentalist >> MS BI Development Update

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |