Monday, March 22, 2021

Searching all Fields in all Tables in a Single Database

Way back in 2015 I provided the SQL to search for a specific string in any field in any table in any database on a server. I don't remember why I needed that, but I guess I did. What I've come to use more frequently, however, is a search of every field in every table in a single database. So here's that query:


DECLARE @SearchText NVARCHAR(1000) = 'Some value'
DECLARE @SchemaName NVARCHAR(256), @TableName NVARCHAR(256), @ColumnName NVARCHAR(256)
DECLARE TableCursor CURSOR FOR
SELECT sch.[Name] AS SchemaName, st.[Name] AS TableName, sc.[Name] AS ColumnName
FROM sys.tables st WITH (NOLOCK)
INNER JOIN sys.columns sc WITH (NOLOCK)
    ON st.object_id = sc.object_id
INNER JOIN sys.schemas sch WITH (NOLOCK)
    ON st.schema_id = sch.schema_id

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @InternalSQL NVARCHAR(MAX) = 'SELECT @CountParam = COUNT(*) FROM [' + @SchemaName + '].[' + @TableName + '] WHERE [' + @ColumnName + '] LIKE ''%' + @SearchText + '%'''
    DECLARE @Count INT

    EXEC SP_EXECUTESQL @InternalSQL, N'@CountParam INT OUT', @Count OUT

    IF (@Count > 0)
    BEGIN
        PRINT @SchemaName + '.' + @TableName + '.' + @ColumnName
    END

    FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName, @ColumnName

END

CLOSE TableCursor
DEALLOCATE TableCursor