Lets Have a fun with Technology.

BTemplates.com

Follow by Email

Tuesday, January 19, 2016

How to find text from all tables of SQL Server (Find Specific Text in All Tables of SQL)


Hello,

Create below store procedure first.




Create PROC SearchInAllTable

(

@searchText nvarchar(max)

)

AS

BEGIN



SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @searchString nvarchar(110)

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

SET  @TableName = ''

SET @searchString = QUOTENAME('%' + @searchText + '%','''')



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 #FinalOutput

            EXEC

            (

                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 

                FROM ' + @TableName + ' (NOLOCK) ' +

                ' WHERE ' + @ColumnName + ' LIKE ' + @searchString

            )

        END

    END 

END



SELECT * FROM #FinalOutput

END






Now Run this store procedure from SSMS.




exec SearchInAllTable 'text which need to search'




Enjoy!!!

0 comments:

Post a Comment