Scan a SQL Server Database for Objects and Columns Containing a Given Text Value

By: Ken Simmons   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Scripts

Problem

Last week we had a conversion and the bank number was going to change during the process. I knew there was a lot of special code written for the bank and I did not want to miss anything. I needed to find an easy way to scan all tables in the database to find any object that contained that specific bank number or any columns in the database that I needed to update.  In this tip I will show you the approach I took to easily identify what columns a particular value exists in as well as the how many times this value exists.

Solution

I will be using the AdventureWorks database for demonstration purposes. Let's say the owner of AdventureWorks comes in one day and says "I never want to see the word bike again. Go update the database to bicycle."

First of all, you can search all the objects that contain the word bike by using the following query.

SELECT OBJECT_NAME(id) ObjectName , [Text]  
FROM syscomments 
WHERE TEXT LIKE '%bike%' 

Which would return a result set similar to this.

Now you can review the objects for any business logic changes that need to be made. That's a good start, but most of the data that feeds the web pages is contained within the columns.

Let's say you know all the columns that could possibly contain the word bike have the word "name" in the column header. You can now execute the following query to return all columns with name in the title and the number of occurrences of the word bike in each column.

DECLARE @SQL VARCHAR(MAX) 
CREATE TABLE #TMP 
   (Clmn VARCHAR(500), 
   CNT INT) 

SELECT @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''' AS Clmn, count(*) CNT FROM '  
        + TABLE_SCHEMA + '.[' + TABLE_NAME + 
       '] WHERE [' + COLUMN_NAME + '] LIKE ''%bike%'' ;'  AS VARCHAR(MAX)) 
FROM INFORMATION_SCHEMA.COLUMNS  
   JOIN sysobjects B  
   ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME 
WHERE COLUMN_NAME LIKE '%Name%' AND xtype = 'U' 
   AND DATA_TYPE NOT IN ('datetime') 

PRINT @SQL 
EXEC(@SQL) 

SELECT * FROM #TMP WHERE CNT > 0 

DROP TABLE #TMP 

The above query will return a result set similar to the following.

Now you can quickly go to the tables and columns to make the correct updates.

To see the rows for a particular table and column such as the Sales.Store.Name you can then issue a query like this.

SELECT *  
FROM Sales.Store  
WHERE Name LIKE '%bike%' 

I have included the DATA_TYPE example in the WHERE clause because changing the like to an equals operation may result in a datatype mismatch error when comparing certain column types. For example "SELECT count(*) FROM table WHERE datecolumn = 'bike' " would result in the following error.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

To take this a bit further here is another version that allows you to plug in the values to be searched and the column name.  In this example we are searching for the value 'David' in all columns.  Also to eliminate the datatype mismatch as mentioned above the DATA_TYPE has been limited to just text data types.

DECLARE @SQL VARCHAR(MAX) 
DECLARE @valueToFind VARCHAR(100) 
DECLARE @columnName VARCHAR(100) 

SET @valueToFind = 'David' 
SET @columnName = '%%' 

CREATE TABLE #TMP 
   (Clmn VARCHAR(500), 
   CNT INT) 

SELECT @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''' AS Clmn, count(*) CNT FROM '  
        + TABLE_SCHEMA + '.[' + TABLE_NAME + 
       '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @valueToFind + '%'' ;'  AS VARCHAR(MAX)) 
FROM INFORMATION_SCHEMA.COLUMNS  
   JOIN sysobjects B  
   ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME 
WHERE COLUMN_NAME LIKE @columnName AND xtype = 'U' 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') 

PRINT @SQL 
EXEC(@SQL) 

SELECT * FROM #TMP WHERE CNT > 0 
DROP TABLE #TMP 

The above query will return a result set similar to the following.

Next Steps

• I was able to avoid a cursor based solution here by using the coalesce function. Take a look at the statements that print out in the messages tab to better understand the script.
• The script can be used in SQL Server 2000 also by replacing the VARCHAR(MAX) at the top of the script with VARCHAR(8000), but be careful because if the search produces an output string longer than 8000 characters you will get an error message.
• Also, the more specific you are in your search criteria, the shorter the search string.

About the author

Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips