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 exaple 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