SQL Script to Search an Entire Database for a Value

A few months ago I wrote an ad-hoc SQL script to help with the frustrating task of, “Where is this value coming from in the database?” It can be incredibly useful if you are inheriting a database from another team, or are developing on an open-source database application. The script uses SQLServer 2005 or later to cycle through each table and each column* in all tables to see if that column is equal to some string or value we’re seeking. It then returns three result sets:

  • List of tables that had a column matching the search criteria
  • Table/column pairs of every column that satisfied the WHERE clause
  • SQL queries you may run in order to retrieve the matching results (one query per table), with the SELECT returning the matching columns before the full set of columns from the table

Continue reading to view and download the SQL script.

*I decided to apply a filter by column type on which columns to search, in case the types of some columns throw casting errors on your search value or has other general problems with the WHERE clause.
If you want to see the full list of types in your database, you can run the following query:

SELECT DISTINCT DATA_TYPE
FROM Information_Schema.Columns

Download the Source Code

-- Variables to modify
-- @ColumnTypes: Comma-separated value of database types. The script only performs the search clause on
-- @SearchString: The value to search for.
-- @SearchClause: The template of the

DECLARE @SearchString NVARCHAR(MAX),
@SearchClause NVARCHAR(MAX),
@ColumnTypes NVARCHAR(MAX)

--SET @ColumnTypes = 'INT'
--SET @ColumnTypes = 'xml,char,nchar,ntext,nvarchar,text,uniqueidentifier,varchar'

--------------------------------------------------------------------------------------
-- Modify @ColumnTypes to set the type of columns to search on
--------------------------------------------------------------------------------------
SET @ColumnTypes = 'xml,char,nchar,ntext,nvarchar,text,uniqueidentifier,varchar'


--------------------------------------------------------------------------------------
-- Modify @SearchString to set the value you're searching on
--------------------------------------------------------------------------------------
SET @SearchString = 'Waldorf'

IF(LEN(ISNULL(@SearchClause, '')) = 0)
BEGIN
--------------------------------------------------------------------------------------
-- Modify @SearchClause change your search criteria (i.e. = v.s. LIKE, if you want % in your LIKE search clauses. CAST/etc
-- may also go here. [<>] is replaced by the name of the column
--------------------------------------------------------------------------------------
SET @SearchClause = '[<>] LIKE ''%' + @SearchString + '%'''
END

-- Table used for storing the (table, column)-pairs that satisfy our @SearchClause
DECLARE @SearchResultRow TABLE
(
TableName NVARCHAR(512),
ColumnName NVARCHAR(512),
OrdinalPosition INT
)

DECLARE @Column TABLE
(
TableName NVARCHAR(512),
ColumnName NVARCHAR(512),
DataType NVARCHAR(512),
CharacterMaximumLength INT,
OrdinalPosition INT
)

-- Get list of all columns in the database that are of the column type we're searching on.
INSERT INTO @Column
(
TableName,
ColumnName,
DataType,
CharacterMaximumLength,
OrdinalPosition
)
SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
ORDINAL_POSITION
FROM Information_Schema.Columns
WHERE ',' + @ColumnTypes + ',' LIKE '%,' + DATA_TYPE + ',%'

-- Used to keep track of every (table, column)-pair.
DECLARE @TableColumn TABLE
(
TableName NVARCHAR(512),
ColumnName NVARCHAR(512),
DataType NVARCHAR(512),
CharacterMaximumLength INT,
OrdinalPosition INT
)

DECLARE @CurrentTableName NVARCHAR(512)
SET @CurrentTableName =
(
SELECT TOP 1 TableName
FROM @Column
ORDER BY TableName
)

-- The template of the query that is executed on each row from @Column
-- to see if it satisfies our @SearchClause.
DECLARE @SqlTestTemplate NVARCHAR(MAX)
SET @SqlTestTemplate =
'SELECT [TableName],
[ColumnName],
[OrdinalPosition]
FROM
(
SELECT ''[<>]'' AS TableName,
''[<>]'' AS ColumnName,
[<>] AS OrdinalPosition
) AS TableColumn
WHERE EXISTS
(
SELECT [<>]
FROM [<>]
WHERE ' + @SearchClause + '
)'

-- Cycle through and find each (table, column)-pair satisfying our @SearchClause
WHILE(@CurrentTableName IS NOT NULL) -- Cycle through each table.
BEGIN

DELETE
FROM @TableColumn

-- Insert every (table, column)-pair into TableColumn for the current Table
INSERT INTO
@TableColumn
(
TableName,
ColumnName,
DataType,
CharacterMaximumLength,
OrdinalPosition
)
SELECT TableName,
ColumnName,
DataType,
CharacterMaximumLength,
OrdinalPosition
FROM @Column AS [Column]
WHERE [Column].TableName = @CurrentTableName

-- SELECT *
-- FROM @TableColumn
-- ORDER BY OrdinalPosition

DECLARE @CurrentColumnName NVARCHAR(512),
@OrdinalPosition INT
SET @CurrentColumnName =
(
SELECT TOP 1 ColumnName
FROM @TableColumn AS [TableColumn]
ORDER BY OrdinalPosition
)
SET @OrdinalPosition =
(
SELECT TOP 1 OrdinalPosition
FROM @TableColumn AS [TableColumn]
WHERE [TableColumn].[ColumnName] = @CurrentColumnName
)

WHILE(@CurrentColumnName IS NOT NULL) -- Cycle through each column for the current table.
BEGIN

--SELECT @CurrentTableName, @CurrentColumnName

-- Build our sql test for a column from the @SqlTestTemplate variable.
DECLARE @ColumnTestSql NVARCHAR(MAX)
SET @ColumnTestSql = REPLACE(@SqlTestTemplate, '[<>]', '[' + @CurrentTableName + ']')
SET @ColumnTestSql = REPLACE(@ColumnTestSql, '[<>]', '[' + @CurrentColumnName + ']')
SET @ColumnTestSql = REPLACE(@ColumnTestSql, '[<>]', CAST(@OrdinalPosition AS NVARCHAR(512)))

-- PRINT @ColumnTestSql

-- Insert record of (table, column)-pair satisfying our criteria.
INSERT INTO @SearchResultRow
(
TableName,
ColumnName,
OrdinalPosition
)
EXEC sp_executesql @ColumnTestSql


-- Get the next column we're going to search on and get its ordinal position (for looping
-- through the columns in a table).

SET @CurrentColumnName =
(
SELECT TOP 1 ColumnName
FROM @TableColumn AS [TableColumn]
WHERE OrdinalPosition > @OrdinalPosition
ORDER BY OrdinalPosition
)

SET @OrdinalPosition =
(
SELECT TOP 1 OrdinalPosition
FROM @TableColumn AS [TableColumn]
WHERE [TableColumn].[ColumnName] = @CurrentColumnName
)

END -- WHILE(@CurrentColumnName IS NOT NULL)


-- Get next table we going to test (for looping through our list of tables)
SET @CurrentTableName =
(
SELECT TOP 1 TableName
FROM @Column AS [Column]
WHERE [Column].TableName > @CurrentTableName
ORDER BY TableName
)
END -- WHILE(@CurrentTableName IS NOT NULL)


--------------------------- -----------------------------------------------------
--------------------------------------------------------------------------------
------------------- The list of all tables that have a value that --------------
------------------- satisfies our SearchClause in at least one row -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SELECT DISTINCT TableName
FROM @SearchResultRow AS SearchResultRow
ORDER BY TableName


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------- The list of all (table, colum)-pairs that have a value that -----------
------------------- satisfies our SearchClause in at least one row -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SELECT TableName, ColumnName
FROM @SearchResultRow AS SearchResultRow
ORDER BY TableName, OrdinalPosition, ColumnName



--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------- Building Queries -----------------------------------
-- This code is for building the select/sql queries for getting the matching ---
-- rows from each of the tables that we know has a matching value somewhere ----
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------


DECLARE @SearchResultQuery TABLE
(
TableName NVARCHAR(512),
ColumnList NVARCHAR(MAX),
WhereClause NVARCHAR(MAX)
)

DECLARE @QueryTableName NVARCHAR(512)
DECLARE @QueryColumn NVARCHAR(512)

SET @QueryTableName =
(
SELECT TOP 1 TableName
FROM (
SELECT DISTINCT TableName
FROM @SearchResultRow AS SearchResultRow
) AS MatchingTable
ORDER BY TableName
)


WHILE(@QueryTableName IS NOT NULL)
BEGIN

DECLARE @QueryColumnName NVARCHAR(512),
@QueryColumnOrdinalPosition INT

SET @QueryColumnName = (
SELECT TOP 1 ColumnName
FROM @SearchResultRow AS SearchResultRow
WHERE TableName = @QueryTableName
ORDER BY TableName, OrdinalPosition, ColumnName
)
SET @QueryColumnOrdinalPosition = (
SELECT TOP 1 OrdinalPosition
FROM @SearchResultRow AS SearchResultRow
WHERE TableName = @QueryTableName
AND ColumnName = @QueryColumnName
)

INSERT INTO @SearchResultQuery
(
TableName,
ColumnList,
WhereClause
)
VALUES
(
@QueryTableName,
'',
''
)

WHILE(@QueryColumnName IS NOT NULL)
BEGIN

DECLARE @ColumnList NVARCHAR(MAX),
@WhereClause NVARCHAR(MAX),
@AdditionalWhereClause NVARCHAR(MAX)

SELECT @ColumnList = ColumnList,
@WhereClause = WhereClause
FROM @SearchResultQuery AS SearchResultQuery
WHERE SearchResultQuery.TableName = @QueryTableName

SET @AdditionalWhereClause = REPLACE(@SearchClause, '[<>]', @QueryColumnName)

IF(LEN(@WhereClause) <> 0)
BEGIN
SET @WhereClause = @WhereClause + ' OR '
END

SET @WhereClause = @WhereClause + @AdditionalWhereClause

IF(LEN(@ColumnList) <> 0)
BEGIN
SET @ColumnList = @ColumnList + ',
'
END

SET @ColumnList = @ColumnList + @QueryColumnName

UPDATE @SearchResultQuery
SET ColumnList = @ColumnList,
WhereClause = @WhereClause
WHERE TableName = @QueryTableName

-- PRINT @QueryTableName + ': ' + @QueryColumnName

SET @QueryColumnName = (
SELECT TOP 1 ColumnName
FROM @SearchResultRow AS SearchResultRow
WHERE SearchResultRow.TableName = @QueryTableName
AND SearchResultRow.OrdinalPosition > @QueryColumnOrdinalPosition
ORDER BY TableName, OrdinalPosition, ColumnName
)

SET @QueryColumnOrdinalPosition = (
SELECT TOP 1 OrdinalPosition
FROM @SearchResultRow AS SearchResultRow
WHERE TableName = @QueryTableName
AND ColumnName = @QueryColumnName
)
END
------------------------ WHILE(@QueryColumnName IS NOT NULL)

SET @QueryTableName =
(
SELECT TOP 1 TableName
FROM (
SELECT DISTINCT TableName
FROM @SearchResultRow AS SearchResultRow
WHERE SearchResultRow.TableName > @QueryTableName
) AS MatchingTable
ORDER BY TableName
)

END
------------------------- WHILE(@QueryTableName IS NOT NULL)



------------------------------------------------------------------
-------- Spit out the sql queries we were just building ----------
------------------------------------------------------------------

SELECT '
SELECT ' + ColumnList + ', *
FROM ' + TableName + '
WHERE ' + WhereClause
FROM @SearchResultQuery AS SearchResultQuery


PRINT 'Done'

« Prev Article
Next Article »