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:
- The first restriction is that I limit the search field to only VARCHAR columns.
- 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.
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
4 thoughts on “T-SQL – Search a Database for all Table Columns with a Text Value”
Reblogged this on JavaShine and commented:
Useful SQL script for searching the text across the database
Useful information tat gives the position of a data in the database INFORMATION_SCHEMA.COLUMNS gives the column number where as INFORMATION_SCHEMA.rows give the row number with many such queries we can also find the position of particular table in the database