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


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

Steven Jennings
Steven has more than five years of Microsoft Dynamics CRM implementation expertise. As CRM Team Lead at Affiliated, his main focus is in sales, marketing, and service areas. He has a strong background designing and deploying integrated solutions, he also has over eight years of .NET development expertise. Steven is proficient in all phases of project development, from concept through realization and assists in the management of a team of analysts and developers responsible for the organization's applications development and analysis functions. He evaluates existing applications to determine technical changes, schedules projects and resources, and monitors project timelines. For more information or to contact Steven, feel free to email him here.
No comments yet.

Leave a Reply

Capcha * Time limit is exhausted. Please reload the CAPTCHA.

Follow

Follow this blog

Email address