Recently I came across a strange requirement to search the whole database for a specific string in SQL Server. That is, we have to search for a keyword in almost all the columns in all the tables in a database. On googling, I found a similar question in stack overflow which has a wonderful stored procedure by Narayana Vyas to search the database. Though the stored procedure was created for SQL Server 7.0 & 2000, it still works in SQL Server 2019. Anyway, I have done few changes to the stored procedure to reduce the number of while loops and also included text and ntext columns and a few more minor changes to fit my needs.
Here is the modified stored procedure to search the whole database for a string. Be aware that this stored procedure takes around 5 to 7 seconds to run in the SQL Server sample database WideWorldImporters. It may take longer time in bigger databases.
This stored procedure returns a table with two columns, the first column will have the Schema Name + Table Name + Column Name. The second column will have the actual content of the column.
CREATE PROCEDURE mtbSearchDatabase
(
@SearchStr nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SearchResults TABLE(TableAndColumnName nvarchar(512), ColumnValue nvarchar(max));
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(256), @TableAndColumnName nvarchar(512),
@TableAndColumnName2 nvarchar(512), @SearchStr2 nvarchar(110);
SET @TableAndColumnName = '';
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''');
WHILE @TableAndColumnName IS NOT NULL
BEGIN
SELECT TOP 1 @TableAndColumnName = QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)+ '.' + QUOTENAME(COLUMN_NAME),
@TableName = QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME),
@ColumnName = QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
WHERE OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)+ '.' + QUOTENAME(COLUMN_NAME) > @TableAndColumnName
AND DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')
ORDER BY QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME), QUOTENAME(COLUMN_NAME);
IF @TableAndColumnName != ISNULL(@TableAndColumnName2, '')
BEGIN
SET @TableAndColumnName2 = @TableAndColumnName;
INSERT INTO @SearchResults
EXEC ('SELECT ''' + @TableAndColumnName + ''', ' + @ColumnName +
' FROM ' + @TableName + ' WITH (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
);
END
ELSE
BEGIN
BREAK;
END
END
SELECT TableAndColumnName, ColumnValue FROM @SearchResults
END
GO
Reference
- Article by Narayana Vyas How to search all columns of all tables in a database for a keyword?