SQL Server Made Easy – Finding Specific DATA in a database

Looking for something? 
Let’s say you’re looking for a specific data value in a database, but you don’t know which table or data elements contain the value.  The SQL procedure below will scan all character string data elements in the database for the specified value. 

I. Create the procedure in the required database.  SQL Script below:

CREATE PROC SearchAllTables  (@Search char(100) )
AS
BEGIN
                   CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue char(3630))
                  SET NOCOUNT ON
DECLARE @Table char(256),
@Column char(128),
@XSearch char(110)

                  SET  @Table = ”
                  SET @XSearch = QUOTENAME(‘%’ + @Search + ‘%’,””)
                  WHILE @Table IS NOT NULL
                  BEGIN
                             SET @Column = ”
                             SET @Table =
                             (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ +
                             QUOTENAME(TABLE_NAME))
                             FROM  INFORMATION_SCHEMA.TABLES
                             WHERE TABLE_TYPE = ‘BASE TABLE’
                             AND QUOTENAME(TABLE_SCHEMA) + ‘.’ +
                             QUOTENAME(TABLE_NAME) > @Table
                             AND OBJECTPROPERTY(OBJECT_ID
                             (QUOTENAME(TABLE_SCHEMA) + ‘.’ +  QUOTENAME (TABLE_NAME)), ‘IsMSShipped’) = 0)
                WHILE (@Table IS NOT NULL) AND (@Column IS NOT NULL)
                BEGIN
                            SET @Column =
                           (SELECT MIN(QUOTENAME(COLUMN_NAME))
                           FROM  INFORMATION_SCHEMA.COLUMNS
                           WHERE TABLE_SCHEMA= PARSENAME(@Table, 2)
                           AND TABLE_NAME = PARSENAME(@Table, 1)
                           AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
                           AND QUOTENAME(COLUMN_NAME) > @Column)
                           IF @Column IS NOT NULL
                           BEGIN
                                       INSERT INTO #Results
                                       EXEC (‘SELECT ”’ + @Table+ ‘.’ + @Column + ”’,
                                                   LEFT(‘ + @Column + ‘, 3630)
                                                   FROM ‘ + @Table+ ‘ (NOLOCK) ‘ +
                                                   ‘ WHERE ‘ + @Column + ‘ LIKE ‘ + @XSearch)
                                                  END
                                      END 
                          END

            SELECT ColumnName, ColumnValue FROM #Results
END
 
II. To search all columns of all tables in database for the keyword “Computer”

EXEC SearchAllTables ‘value’

This may be a long running process.  The amount of time to execute is dependent on the size of the database you are scanning.