Based on http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm and http://stackoverflow.com/a/436676/412368. Updated 2025-04-28.
Supports searching both column names, text and numbers values.
Optimized, roughly 4-5 times faster than earlier versions.
Execute
SET NOCOUNT ON;
DECLARE @SearchStr NVARCHAR(100) = 'mydata';
DECLARE @Results TABLE
(
ColumnName NVARCHAR(370),
ColumnValue NVARCHAR(3630)
);
DECLARE @TableName NVARCHAR(256);
DECLARE @ColumnName NVARCHAR(128);
DECLARE @DataType NVARCHAR(128);
DECLARE @SearchStr2 NVARCHAR(110);
DECLARE @SearchDecimal DECIMAL(38,19);
DECLARE @Query NVARCHAR(4000);
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''');
SET @SearchDecimal = CASE WHEN ISNUMERIC(@SearchStr) = 1 THEN CONVERT(DECIMAL(38,19), @SearchStr) ELSE NULL END;
PRINT '@SearchStr2: ' + @SearchStr2;
PRINT '@SearchDecimal: ' + CAST(@SearchDecimal AS NVARCHAR);
SET @TableName = '';
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = '';
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY
(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
'IsMSShipped'
) = 0
);
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SELECT
@ColumnName = MIN(QUOTENAME(COLUMN_NAME)),
@DataType = MAX(DATA_TYPE)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'bigint', 'tinyint', 'numeric', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName;
PRINT @TableName + '.' + @ColumnName + ' (' + @DataType + ')';
IF @ColumnName IS NOT NULL
BEGIN
SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', ''COLUMN NAME MATCH'' WHERE ''' + @ColumnName + ''' LIKE ' + @SearchStr2;
PRINT ' ' + @Query;
INSERT INTO @Results
EXEC (@Query);
IF @DataType IN ('int', 'bigint', 'tinyint', 'numeric', 'decimal')
BEGIN
IF @SearchDecimal IS NOT NULL
BEGIN
SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + ' AS NVARCHAR(110)), 3630) ' +
'FROM ' + @TableName + ' (NOLOCK) ' +
'WHERE ' + @ColumnName + ' = ' + CAST(@SearchDecimal AS NVARCHAR);
PRINT ' ' + @Query;
INSERT INTO @Results
EXEC (@Query);
END
END
ELSE
BEGIN
SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' +
'FROM ' + @TableName + ' (NOLOCK) ' +
'WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2;
PRINT ' ' + @Query;
INSERT INTO @Results
EXEC (@Query);
END
END
END
END
SELECT ColumnName, ColumnValue
FROM @Results;
Set @SearchStr in the script and execute directly.
Alternative: Install stored procedure
IF OBJECT_ID('dbo.SearchAllTables', 'P') IS NOT NULL
DROP PROCEDURE dbo.SearchAllTables;
GO
CREATE PROC dbo.SearchAllTables
(
@SearchStr NVARCHAR(100)
)
AS
BEGIN
-- Purpose: To search all columns of all tables and data for a given search string
-- Customized and modified: 2025-04-28, 2014-01-21
-- Tested on: SQL Server 2022, 2008 R2
DECLARE @Results TABLE
(
ColumnName NVARCHAR(370),
ColumnValue NVARCHAR(3630)
);
SET NOCOUNT ON;
DECLARE @TableName NVARCHAR(256);
DECLARE @ColumnName NVARCHAR(128);
DECLARE @DataType NVARCHAR(128);
DECLARE @SearchStr2 NVARCHAR(110);
DECLARE @SearchDecimal DECIMAL(38,19);
DECLARE @Query NVARCHAR(4000);
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''');
SET @SearchDecimal = CASE WHEN ISNUMERIC(@SearchStr) = 1 THEN CONVERT(DECIMAL(38,19), @SearchStr) ELSE NULL END;
PRINT '@SearchStr2: ' + @SearchStr2;
PRINT '@SearchDecimal: ' + CAST(@SearchDecimal AS NVARCHAR);
SET @TableName = '';
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = '';
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY
(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
'IsMSShipped'
) = 0
);
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SELECT
@ColumnName = MIN(QUOTENAME(COLUMN_NAME)),
@DataType = MAX(DATA_TYPE)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'bigint', 'tinyint', 'numeric', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName;
PRINT @TableName + '.' + @ColumnName + ' (' + @DataType + ')';
IF @ColumnName IS NOT NULL
BEGIN
SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', ''COLUMN NAME MATCH'' WHERE ''' + @ColumnName + ''' LIKE ' + @SearchStr2;
PRINT ' ' + @Query;
INSERT INTO @Results
EXEC (@Query);
IF @DataType IN ('int', 'bigint', 'tinyint', 'numeric', 'decimal')
BEGIN
IF @SearchDecimal IS NOT NULL
BEGIN
SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(CAST(' + @ColumnName + ' AS NVARCHAR(110)), 3630) ' +
'FROM ' + @TableName + ' (NOLOCK) ' +
'WHERE ' + @ColumnName + ' = ' + CAST(@SearchDecimal AS NVARCHAR);
PRINT ' ' + @Query;
INSERT INTO @Results
EXEC (@Query);
END
END
ELSE
BEGIN
SET @Query = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' +
'FROM ' + @TableName + ' (NOLOCK) ' +
'WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2;
PRINT ' ' + @Query;
INSERT INTO @Results
EXEC (@Query);
END
END
END
END
SELECT ColumnName, ColumnValue
FROM @Results;
END
GO
Usage
exec dbo.SearchAllTables 'mydata'