Thursday, September 9, 2010

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