As a SQL Developer or DBA, we sometimes need to find where a specific value exists across the database. Manually checking hundreds of tables can be time-consuming.
This SQL Server script provides a simple way to search a value across all dbo tables and supported data types.
How It Works
The script:
Accepts a search value such as Employee ID, Email, Name, or Code.
Reads table and column information from
INFORMATION_SCHEMA.Automatically identifies the column data type.
Builds Dynamic SQL based on the data type.
Searches matching records across the database.
Returns the Table Name, Column Name, Match Count, and Sample Value.
Provides a summary of total columns searched and total matches.
Example
If we search:
DECLARE @SearchValue NVARCHAR(MAX) = '10245';The result may look like:
Table Name | Column Name | Match Count | Sample Value |
|---|---|---|---|
EmployeeDetails | EmployeeID | 1 | 10245 |
EmployeeSalary | EmployeeID | 1 | 10245 |
EmployeeAttendance | EmployeeID | 125 | 10245 |
This makes it easy to identify where a particular value is stored and how frequently it appears.
Key SQL Concepts Used
INFORMATION_SCHEMA → Reads database metadata.
Dynamic SQL → Builds queries dynamically for different tables and columns.
sp_executesql → Executes the generated SQL.
QUOTENAME() → Safely handles table and column names.
#Temporary Table → Stores and summarizes search results.
Practical Use Cases
This utility is helpful for data investigation, production issue debugging, impact analysis, legacy database analysis, and data tracing.
⚠️ Note: Database-wide searches can be expensive on large production databases, so test and use them carefully.
DECLARE @SearchValue NVARCHAR(MAX) = 'YOUR_SEARCH_VALUE_HERE'
DECLARE @SearchLike NVARCHAR(MAX) = '%' + REPLACE(@SearchValue, '%', '[%]') + '%'
DECLARE @UseNumericSearch BIT = ISNUMERIC(@SearchValue)
-- Temp table for results
IF OBJECT_ID('tempdb..#SearchResults', 'U') IS NOT NULL
DROP TABLE #SearchResults
CREATE TABLE #SearchResults
(
TableName NVARCHAR(255),
ColumnName NVARCHAR(255),
MatchCount INT,
SampleValue NVARCHAR(1000)
)
-- Index on temp table for better performance
CREATE CLUSTERED INDEX IX_SearchResults ON #SearchResults(TableName, ColumnName)
-- Dynamic SQL construction
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @CRLF CHAR(2) = CHAR(13) + CHAR(10)
-- Build complete search script
SELECT @SQL = @SQL + @CRLF +
'INSERT INTO #SearchResults SELECT ''' + t.TABLE_NAME + ''', ''' + c.COLUMN_NAME + ''', ' +
'COUNT(*), CAST(MIN(' + QUOTENAME(c.COLUMN_NAME) + ') AS NVARCHAR(1000)) FROM ' +
QUOTENAME(t.TABLE_NAME) + ' WHERE ' +
CASE
WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
THEN QUOTENAME(c.COLUMN_NAME) + ' LIKE N''' + @SearchLike + ''''
WHEN c.DATA_TYPE IN ('int', 'bigint', 'smallint', 'tinyint') AND @UseNumericSearch = 1
THEN QUOTENAME(c.COLUMN_NAME) + ' = CAST(''' + @SearchValue + ''' AS INT)'
WHEN c.DATA_TYPE IN ('decimal', 'numeric', 'float', 'real') AND @UseNumericSearch = 1
THEN 'CAST(' + QUOTENAME(c.COLUMN_NAME) + ' AS NVARCHAR(50)) LIKE N''' + @SearchLike + ''''
WHEN c.DATA_TYPE IN ('datetime', 'datetime2', 'date')
THEN 'CAST(' + QUOTENAME(c.COLUMN_NAME) + ' AS NVARCHAR(50)) LIKE N''' + @SearchLike + ''''
ELSE '1=0'
END + @CRLF
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_SCHEMA = 'dbo'
AND c.DATA_TYPE IN (
'varchar', 'nvarchar', 'char', 'nchar',
'int', 'bigint', 'smallint', 'tinyint',
'decimal', 'numeric', 'float', 'real',
'datetime', 'datetime2', 'date'
)
ORDER BY t.TABLE_NAME, c.COLUMN_NAME
-- Execute the constructed SQL
BEGIN TRY
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'Error executing search: ' + ERROR_MESSAGE()
END CATCH
-- Return results - only matching records
SELECT
TableName,
ColumnName,
MatchCount,
SampleValue,
'[' + TableName + '].[' + ColumnName + ']' AS FullPath
FROM #SearchResults
WHERE MatchCount > 0
ORDER BY MatchCount DESC, TableName, ColumnName
-- Show summary
SELECT
COUNT(*) AS TotalColumnsSearched,
SUM(CASE WHEN MatchCount > 0 THEN 1 ELSE 0 END) AS ColumnsWithMatches,
SUM(MatchCount) AS TotalMatches,
@SearchValue AS SearchedValue
FROM #SearchResults
DROP TABLE #SearchResults