T-SQL – Search a Database for all Table Columns with a Text Value

There are times when it comes in handy to be able to query all columns of all of the tables in a database for a particular text match. For example: if you have just taken over a project with a large database and are trying to find where values from the UI are coming from, then it helps to be able to match text from the UI with tables and columns.

So you can see how being able to use a T-SQL query to locate the table and column responsible for holding specific data saves a lot of time. Rather than searching through the database diagram and guessing at tables and columns, you can simply run a blanket search to find out where the columns you need are.

In this article I’m providing a fully working example of a query that will search your database for any specific text that you may be interested in. I have added two restrictions to the example since it can take a longer time to run depending on the size of your database and the frequency that your keyword appears in:

1. The first restriction is that I limit the search field to only VARCHAR columns.
2. The second restriction is that the query will only return ten results per column searched.

How it works

The core of the query is a query to the systems tables that I covered in an earlier article. This query lets you search for table column information using a query to the inbuilt INFORMATION_SCHEMA.COLUMNS view. With this query in place, we can easily return a list of all tables and their columns that exist in the current database.

The trick then is to set up a cursor to the INFORMATION_SCHEMA.COLUMNS view so that as we loop through the database tables and columns, we can run a query against each for the text we want to find.

For ease of use, the query populates a table variable with the results of the search. This has the added benefit of only returning positive matches when the query is complete and gives the programmer further control over the results if necessary. The query uses an EXEC statement to populate the results of the search with the text that has been matched as well as the table and column names where the match took place.

The Query

Here is the code to query all table columns in a database for particular text. To run this, simply copy and paste it into your SQL Server Management Studio and adjust the search text to what you would like to fin. In the below example I have highlighted the line where you can enter your search text in green.

 DECLARE @ColName VARCHAR(500)
 DECLARE @TableName VARCHAR(500)
 DECLARE @SearchValue VARCHAR(500)
 DECLARE @ResultsTable TABLE(
 ResultInfo VARCHAR(500),
 ColName VARCHAR(500),
 TableName VARCHAR(500)
 )
SET @SearchValue = 'New'
 DECLARE MY_CURSOR Cursor
 FOR
 SELECT
 COLUMN_NAME,TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE = 'varchar'
 ORDER BY TABLE_NAME,COLUMN_NAME ASC
 OPEN MY_CURSOR
 FETCH NEXT FROM MY_CURSOR INTO @ColName,@TableName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 INSERT INTO @ResultsTable
 EXEC('SELECT TOP 10 ' + @ColName + ',''' + @ColName + ''',''' + @TableName + ''' FROM ' + @TableName + ' WHERE ' + @ColName + ' LIKE ''%' + @SearchValue + '%'' ' )
 FETCH NEXT FROM MY_CURSOR INTO @ColName,@TableName
 END
 CLOSE MY_CURSOR
 DEALLOCATE MY_CURSOR
SELECT * FROM @ResultsTable