How to search all columns of all tables in a database for a keyword

Products

• IT Management Suite

Issue/Introduction

Question
How do I search all columns of all tables in a database for a keyword?

Resolution

Answer

The following was pulled from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21634412.html?qid=21634412:

This stored procedure named "SearchAllTables" will accept a search string as input parameter, go and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database.

The output of this stored procedure contains two columns:

1. The table name and column name in which the search string was found
2. The actual content/value of the column (only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (It does use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:

CREATE PROC SearchAllTables( @SearchStr nvarchar(100))
    AS
 BEGIN
     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
                                          EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''',
                                                 LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + '
                                                 (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 )
                                END
                END
       END
SELECT ColumnName, ColumnValue
  FROM #Results
   END