MS SQL Server: Find a Value In Any Field In Any Table

I'm currently working on a Data Conversion and I've been trying to discover where certain fields are used within the database. I have a schema, but it doesn't contain all of the foreign keys as you would hope. So, I can find the values of the field but I need to know which other tables that this might be used in. Example if you have a Control File that is the home for multiple picklist lookups, it should be in there.

Well, I search the Internet for a solution to this and Matt had an answer. Warning, this script will take a long time to run... Duh! it is searching every text field in the entire database for the value.

I did modify the code Matt wrote to tell me where it was at at it was going along.... On my VPC with the amount of data I'm looking at, it could be hours and I would like to know where it is at and when it finds something.

DECLARE @value VARCHAR(64)
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
DECLARE @Oldtable VARCHAR(64)
DECLARE @Count int
DECLARE @Count1 int
 
SET NOCOUNT ON
 
set @Count  =0
set @Count1 =0
set @Oldtable =''
SET @value = 'MyValueToSearchFor'
 
CREATE TABLE #t (
    tablename VARCHAR(64),
    columnname VARCHAR(64)
)
 
DECLARE TABLES CURSOR  
FOR  
  
    SELECT o.name, c.name  
    FROM syscolumns c   
    INNER JOIN sysobjects o ON c.id = o.id   
    WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)   
    ORDER BY o.name, c.name  
  
OPEN TABLES  
 
FETCH NEXT FROM TABLES   
INTO @table, @column  
  
WHILE @@FETCH_STATUS = 0   
BEGIN  
    SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '  
    SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) = ''' + @value + ''') '  
    SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''  
    SET @sql = @sql + @column + ''')'  
    EXEC(@sql)   
    IF (@Oldtable <> @table) 
 BEGIN
  Print 'Searching table ' +@table
  set @Oldtable = @table
 END
 SELECT @Count1=COUNT(*)  from #t
 if (@Count <> @Count1)
 BEGIN
  print '    **FOUND*** in ' +  @column
  SET @Count = @Count1
 END
 
    FETCH NEXT FROM TABLES   
    INTO @table, @column  
END  
  
CLOSE TABLES   
DEALLOCATE TABLES   
  
SELECT *   
FROM #t  
 
DROP TABLE #t

Matt Wrote

---

As part of a project at work, I found myself needing to update every field in an entire database that contained a certain value. If I had needed to do this in a database I had created or a database that didn’t have hundreds of tables, I might have done it manually like I have done it in the past. But that wasn’t the case, so I needed to find a better solution.

I talked to a coworker about the problem and he reminded me about the system tables that every MS SQL Server database contains. In these tables, SQL Server stores information about all the tables, fields, stored procedures, etc. in your database. They can be queried just like any other tables, and provide a handy way to write some useful dynamic queries.

 

Let’s begin. We’re going to be looking at two of the system tables: sysobjects, which is where we will find the tables in our database, and syscolumns, which is where we will find the columns in those tables. We will also be required to use a cursor in this query.

The first step is to declare some variables that will be used. These variables will store the value we are searching for, the dynamic SQL query, and the table and column names which we will use to build the query. There will also be a temporary table created to store the results of the query.

DECLARE @value VARCHAR(64)
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)

SET @value = 'whatever'

CREATE TABLE #t (
    tablename VARCHAR(64),
    columnname VARCHAR(64)
)

Next we will declare the cursor and load with with the data we will be using. The select statement used will be pulling the table and column names by joining together the sysobjects and syscolumns tables.

There are a few things to note. We are only looking at records in the sysobjects table with the type field set to ‘U’ which represents user tables. Also, in this example we are searching for a string, so we’re only looking at records in syscolumns that are CHAR, NCHAR, VARCHAR, and NVARCHAR. You can find the numeric values for the different data types in systypes.

DECLARE TABLES CURSOR
FOR

    SELECT o.name, c.name
    FROM syscolumns c
    INNER JOIN sysobjects o ON c.id = o.id
    WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
    ORDER BY o.name, c.name

OPEN TABLES

Now we’ll use the data stored in the cursor to build and run the dynamic queries.

FETCH NEXT FROM TABLES
INTO @table, @column

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
    SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) = ''' + @value + ''') '
    SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
    SET @sql = @sql + @column + ''')'

    EXEC(@sql)

    FETCH NEXT FROM TABLES
    INTO @table, @column
END

CLOSE TABLES
DEALLOCATE TABLES

SELECT *
FROM #t

In this example, I just select the results of the dynamic query, but you could do many other more useful things.

Oh, and don’t forget to delete that temporary table when you’re done.

DROP TABLE #t