Query to display all tables in MS SQL containing a particular string.

Below query will display all tables in MS SQL database containing a particular string.

I hope this query will help many who is in project support.

declare @String2Search nvarchar(100)

set @String2Search = 'Type Search string'

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

SET NOCOUNT ON

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

SET @TableName = ''

SET @String2Search2 = QUOTENAME('%' + @String2Search + '%','''')

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

                      EXEC

                      (

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

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

                           ' WHERE ' + @ColumnName + ' LIKE ' + @String2Search2

                      )

                 END

            END     

       END

       SELECT ColumnName, ColumnValue FROM #SearchResults