How to search a value in entire database

Question: How to search a value in entire database

Answer:
We created on Stored Procedure SearchAllTables
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)


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


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

How to execute

EXEC SearchAllTables ‘Sujeet’

GO

If you have any doubt please reply to this post else mail me on sujeet.bhujbal@gmail.com

Regards

Sujeet