T-SQL – Find Where Table Data is Stored

In this article  I will expand on an article I wrote earlier about how to search through all of the columns in each table in your database for a particular piece of information.

In the example in this article I have:

1. Modified my previous example query to search through columns of all datatypes rather than just VARCHAR type columns. This is important since often tables will have columns consisting of custom datatypes or possibly even Int value columns depending on your search.
2. I’ve changed what is output to be the distinct table name and column names that contain the information you are looking for. You’ll be able to take useful looking results from this overview information and then run detailed queries to find out more.
3. Also, I have modified the original query to do an exact match for the data being searched for rather than a LIKE condition. If you would like to change this back to a LIKE condition, then I have highlighted the relevant section in red that you can replace with the LIKE condition example in my previous article.This exact match is useful in particular if you are looking for an identity key value throughout your database such as: show me the user Jim who has an ID of 56 so that I can track down all instances of his account information in our database.

The Example Query Used to Scan your Database for Table Columns Containing Data

So here is the example query you can run in SSMS that will search all columns of all of the tables in an MS SQL Server database for an exact match of a particular piece of data. The result of this query will be a list of tables with their columns which contain the data you are searching for.

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 = 'MySearchText'
 DECLARE MY_CURSOR Cursor
 FOR
 SELECT
 COLUMN_NAME,TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 ORDER BY TABLE_NAME,COLUMN_NAME ASC
 OPEN MY_CURSOR
 FETCH NEXT FROM MY_CURSOR INTO @ColName,@TableName
 WHILE @@FETCH_STATUS = 0
 BEGIN
BEGIN TRY
 INSERT INTO @ResultsTable
EXEC('SELECT TOP 10 ' + @ColName + ',''' + @ColName + ''',''' + @TableName + ''' FROM ' + @TableName + ' WHERE CAST(' + @ColName + ' AS VARCHAR(2000)) = ''' + @SearchValue + ''' ' )
END TRY
BEGIN CATCH
-- catch operation
END CATCH;

 FETCH NEXT FROM MY_CURSOR INTO @ColName,@TableName
 END
 CLOSE MY_CURSOR
 DEALLOCATE MY_CURSOR

SELECT DISTINCT TableName,ColName FROM @ResultsTable
ORDER BY TableName,ColName