Find a value anywhere in a database

Question

Given a number, how do I discover in what table and column it could be found within?

I don't care if it's fast, it just needs to work.

Answer 1

This might help you. - from Narayana Vyas. It searches all columns of all tables in a given database. I have used it before and it works.

This is the Stored Proc from the above link - the only change I made was substituting the temp table for a table variable so you don't have to remember to drop it each time.

CREATE PROC SearchAllTables (     @SearchStr nvarchar(100) ) AS BEGIN  -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT  DECLARE @Results TABLE(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 +                  ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2             )         END     END  END  SELECT ColumnName, ColumnValue FROM @Results END 

To execute the stored procedure :

 EXEC SearchAllTables 'YourStringHere'