spSearchTables: Search and Find Tables or Columns by Name or Value

Federico Di Marco

Rate me:

5.00/5 (4 votes)

21 Nov 2023CPOL6 min read

spSearchTables: a helper T-SQL stored procedure for digging into (large) databases

spSearchTables is a helper stored procedure which allows you to search tables or columns either by name or by value in all databases in a server using SQL pattern matching.

Introduction

I have always worked as a consultant switching projects every 1 to 3 years. Every time you switch a project, you have to tackle new databases, which not rarely are undocumented. I have usually dealt with databases of medium sizes (less than 300 tables), heard also of colleagues tackling bigger databases (around 700 tables), but recently I have joined a new project which is huge (several databases with some containing the whopping size of 2000 tables!) and trying to get an understanding of them is sometimes quite troublesome. For this reason, I decided to write this helper stored procedure which allows you to search for databases, tables, columns or column data by using LIKE wildcard syntax.

Background

Using the code is a no-brainer, you just paste the stored procedure code in SSMS and call it using the search options you want. The T-SQL code of the stored procedure is instead complex and tricky, but I think that anyone with an intermediate understanding of T-SQL is able to understand it.

Using the Code

You can check my GitHub repository for the documentation, source code and sample queries on how to use spSearchTables. Here follows the main concepts on the implementation:

The code outputs a table with these columns:
- [Database]: database names matching @dbSearchPattern parameter
- [Schema]: schema name associated with the table
- [Table]: table names matching @tableSearchPattern parameter
- [FullTableName]: it's just the concatenation of database + schema + table
- [MatchingColumns]: comma separated list of column names matching the @columnsSearchPattern and the @valuePattern when it is not null
- [MatchingWhereColumns]: helper column which is not shown in the final output, it is like MatchingColumns but it shows the converted column statement used for LIKE pattern matching (see below).
- [MatchingSelect]: the select statement returning the rows matching the @valuePattern when it is not null (it supports all column datatypes). The matching columns are shown first in the output.
spSearchTables cycles through a cursor after having retrieved a list of all databases by executing the following query on the sys.databases catalog.
SQL
```
SELECT [name]
FROM sys.databases
```
It does not use the Microsoft sp_MSforeachdb since it has the limitation of supporting scripts up to 2000 characters (the one generated by this SP is longer, initially I used sp_MSforeachdb, but after spending almost 2 hours to understand why strange errors happened, I discovered that the input script was being truncated at 2K chars 🤬 ).

For every matching database, it executes this general script passing down the 4 input parameters @valuePattern, @columnSearchPattern, @columnTypeSearchPattern and @schemaSearchPattern:

SQL

USE [?]

DECLARE @dbName nvarchar(200), @schemaName nvarchar(200),_
@tableName nvarchar(200), @columnName nvarchar(200), @columnType nvarchar(200), _
@fullTableName nvarchar(1000)  -- current data
DECLARE @oldDbName nvarchar(200), @oldSchemaName nvarchar(200),_
@oldTableName nvarchar(200), @oldFullTableName nvarchar(1000)  -- old data
DECLARE @whereColumnName nvarchar(200), @whereCondition nvarchar(400), _
@whereClause nvarchar(max), @sql nvarchar(max), @selectSql nvarchar(max), _
@columnListSelect nvarchar(max), @columnList nvarchar(max)  -- helper variables

-- try to parse innerValuePattern with geometry and geography types
DECLARE @geometry geometry, @geography geography, @compatibilityLevel int

BEGIN TRY
    SELECT @compatibilityLevel = compatibility_level _
    FROM sys.databases WHERE database_id=DB_ID()
    SELECT @geography=geography::Parse(@innerValuePattern)
    SELECT @geometry=geometry::Parse(@innerValuePattern)
END TRY
BEGIN CATCH
END CATCH

PRINT N'Checking database [?]'

DECLARE [tables] CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
SELECT N'['+ DB_NAME() +N']' AS DatabaseName,N'['+ s.[name] +N']' _
AS SchemaName,N'['+ t.[name] +N']' AS TableName,N'['+ c.[name] +N']' _
AS ColumnName,tp.[Name] AS ColumnType, _
N'['+DB_NAME()+N'].['+s.[name]+N'].['+t.[name]+N']' AS FullTableName
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id _
AND (@innerSchemaSearchPattern IS NULL OR s.[name] LIKE @innerSchemaSearchPattern)
INNER JOIN sys.columns c ON (@innerColumnSearchPattern IS NULL _
OR c.[name] LIKE @innerColumnSearchPattern) AND c.[object_id]=t.[object_id]
INNER JOIN sys.types tp ON tp.user_type_id = c.user_type_id _
AND (@innerColumnTypeSearchPattern IS NULL OR tp.[name] _
LIKE @innerColumnTypeSearchPattern)
ORDER BY FullTableName

SET @oldFullTableName = NULL

OPEN [tables]

FETCH NEXT FROM [tables] INTO @dbName, @schemaName, @tableName, _
@columnName, @columnType, @fullTableName
WHILE (1=1)
BEGIN
    IF ((@oldFullTableName<>@fullTableName AND @oldFullTableName IS NOT NULL) _
         OR @@FETCH_STATUS<>0)
    BEGIN
        IF (@whereClause IS NOT NULL)
        BEGIN
            SET @whereClause = REPLACE(CONCAT(@whereClause,N'[???]') _
            COLLATE DATABASE_DEFAULT,N'OR [???]',N'')  --trim last "OR "
            SET @selectSql = N'SELECT [??] FROM '+@oldFullTableName+N' _
            WHERE ' + @whereClause 
        END
        ELSE
            SET @selectSql = NULL

        IF (@columnListSelect IS NOT NULL)
        BEGIN
            SET @columnListSelect = N'REPLACE(CONCAT('+@columnListSelect + _
            N'''[???]'') COLLATE DATABASE_DEFAULT,N'',[???]'',N'''')'  -- trim last ","
            SET @columnList = N'REPLACE(CONCAT('+@columnList + N'''[???]'') _
            COLLATE DATABASE_DEFAULT,N'',[???]'',N'''')'  -- trim last ","
            SET @sql = N' INSERT INTO #Output SELECT  '''+@oldDbName+N''','''+_
            @oldSchemaName+N''','''+@oldTableName+N''','''+@oldFullTableName+N''','+_
            @columnList+N','+@columnListSelect+N','''+_
            REPLACE(@selectSql COLLATE DATABASE_DEFAULT,'''','''''')+N''''+_
            IIF(@whereClause IS NOT NULL,' FROM '+@oldFullTableName+' WHERE '+_
            @whereClause,'')
        END
        ELSE IF (@oldDbName IS NOT NULL)
        BEGIN
            SET @columnList = N'REPLACE(CONCAT('+@columnList + N'''[???]'') _
            COLLATE DATABASE_DEFAULT,N'',[???]'',N'''')'  -- trim last ","
            SET @sql = N' INSERT INTO #Output SELECT  '''+@oldDbName+N''','''+_
            @oldSchemaName+N''','''+@oldTableName+N''','''+@oldFullTableName+N''','+_
            @columnList+N',NULL,NULL'
        END

        IF (@selectSql IS NOT NULL)
            SET @sql = N'IF EXISTS ('+REPLACE(@selectSql _
            COLLATE DATABASE_DEFAULT,'[??]','1')+N')' +@sql

        IF (@sql IS NOT NULL)
        BEGIN
            PRINT @sql

            EXECUTE sp_executesql @sql
        END

        IF (@@FETCH_STATUS<>0)
            BREAK

        SET @whereClause = NULL
        SET @columnListSelect = NULL
        SET @columnList = NULL
    END

    IF (@innerValuePattern IS NOT NULL)
    BEGIN
        SET @whereColumnName = (CASE WHEN @columnType _
        COLLATE DATABASE_DEFAULT = N'image' THEN N'CONVERT(NVARCHAR(MAX),_
        CONVERT(VARBINARY(MAX),'+@columnName+N'),1)' WHEN @columnType _
        COLLATE DATABASE_DEFAULT = N'xml' THEN N'CONVERT(nvarchar(MAX),'+_
        @columnName+N')' WHEN @columnType COLLATE DATABASE_DEFAULT _
        IN (N'hierarchyid') THEN @columnName+N'.ToString()' _
        WHEN @columnType COLLATE DATABASE_DEFAULT IN (N'datetime',N'datetime2',_
        N'datetimeoffset',N'time') THEN N'CONVERT(nvarchar(50),'+_
        @columnName+N',126)' ELSE @columnName END)
        
        IF (@columnType COLLATE DATABASE_DEFAULT IN (N'geography',N'geometry'))
        BEGIN    
            IF (@compatibilityLevel>=130 AND ((@columnType _
            COLLATE DATABASE_DEFAULT = N'geometry' AND @geometry IS NOT NULL)
                OR (@columnType COLLATE DATABASE_DEFAULT = N'geography' _
                AND @geography IS NOT NULL)))
                SET @whereCondition = N'COALESCE('+@whereColumnName+N'.STContains_
                ('+IIF(@columnType COLLATE DATABASE_DEFAULT = N'geometry',_
                N'geometry::Parse('''+@geometry.ToString()+N''')),0)=1)',_
                N'geography::Parse('''+@geography.ToString()+N''')),0)=1')
            ELSE
                SET @whereCondition = N'(' + @whereColumnName+N'.ToString() _
                LIKE N'''+@innerValuePattern+N''' COLLATE DATABASE_DEFAULT)'

            SET @whereColumnName = @whereColumnName+N'.ToString()'
        END
        ELSE
            SET @whereCondition = N'(' + @whereColumnName+N' LIKE '''+_
                @innerValuePattern+N''')'

        SET @whereClause = @whereClause + @whereCondition + N' OR '

        SET @columnListSelect = @columnListSelect + N'IIF(SUM(CASE WHEN '+_
        @whereCondition+' THEN 1 ELSE 0 END)>0,'''+@whereColumnName+' _
        AS '+@columnName+','',NULL),'
        SET @columnList = @columnList + N'IIF(SUM(CASE WHEN '+@whereCondition+' _
        THEN 1 ELSE 0 END)>0,'''+@columnName+','',NULL),'
    END
    ELSE
    BEGIN
        SET @whereClause = NULL
        SET @columnListSelect = NULL
        SET @columnList = @columnList + N'''' + @columnName + N','','
    END

    SET @oldDbName = @dbName
    SET @oldSchemaName = @schemaName
    SET @oldTableName = @tableName
    SET @oldFullTableName = @fullTableName

    FETCH NEXT FROM [tables] INTO @dbName, @schemaName, @tableName, _
                                  @columnName, @columnType, @fullTableName
END    

CLOSE [tables]; 
DEALLOCATE [tables];

The general script above selects the outer database in the loop and queries again the system catalogs about its tables, its schemas, its columns together with their datatypes and cycles for all of them.

SQL

SELECT N'[' + DB_NAME() + N']' AS DatabaseName,
       N'[' + s.[name] + N']' AS SchemaName,
       N'[' + t.[name] + N']' AS TableName,
       N'[' + c.[name] + N']' AS ColumnName,
       tp.[Name] AS ColumnType,
       N'[' + DB_NAME() + N'].[' + s.[name] + N'].[' + t.[name] + N']' _
       AS FullTableName
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id _
AND (@innerSchemaSearchPattern IS NULL OR s.[name] LIKE @innerSchemaSearchPattern)
INNER JOIN sys.columns c ON (@innerColumnSearchPattern IS NULL _
OR c.[name] LIKE @innerColumnSearchPattern) AND c.[object_id]=t.[object_id]
INNER JOIN sys.types tp ON tp.user_type_id = c.user_type_id _
AND (@innerColumnTypeSearchPattern IS NULL OR tp.[name] _
LIKE @innerColumnTypeSearchPattern)
ORDER BY FullTableName

The code then gets very complicated and tricky, but basically, it builds and executes one of the two queries below which populate the output table with the information about the filtered tables/columns found in the database.

Query generated when @valuePattern parameter is null:

SQL
```
INSERT INTO #Output
SELECT '[Northwind]',
       '[dbo]',
       '[Order Details]',
       '[Northwind].[dbo].[Order Details]',
       REPLACE(
                  CONCAT('[OrderID],', '[ProductID],', '[UnitPrice],', _
                  '[Quantity],', '[Discount],', '[???]') _
                  COLLATE DATABASE_DEFAULT,
                  N',[???]',
                  N''
              ),
       NULL,
       NULL
```
MatchingColumns is basically a variable length CONCAT with all column names plus comma plus an additional custom postfix ('[???]' which hopefully should not match any table name). The postfix is used to remove the last comma from the last column (the string ',[???]' gets replaced with an empty string through REPLACE function). This is a hack which avoids performing an inner subquery for retrieving the string length in order to trim the last comma with SUBSTRING.

The MatchingWhereColumns / MatchingSelect parameters are instead NULL since we do not query any value.

Query generated when @valuePattern parameter is not null:

SQL

IF EXISTS
(
    SELECT 1
    FROM [AdventureWorks2022].[Person].[Address]
    WHERE ([AddressID] LIKE '%898)')
          OR ([AddressLine1] LIKE '%898)')
          OR ([AddressLine2] LIKE '%898)')
          OR ([City] LIKE '%898)')
          OR ([StateProvinceID] LIKE '%898)')
          OR ([PostalCode] LIKE '%898)')
          OR ([SpatialLocation].ToString() LIKE N'%898)' _
          COLLATE DATABASE_DEFAULT)
          OR ([rowguid] LIKE '%898)')
          OR (CONVERT(nvarchar(50), [ModifiedDate], 126) LIKE '%898)')
)
    INSERT INTO #Output
    SELECT '[AdventureWorks2022]',
           '[Person]',
           '[Address]',
           '[AdventureWorks2022].[Person].[Address]',
           REPLACE(
                      CONCAT(
                                IIF(SUM(CASE WHEN ([AddressID] LIKE '%898)') _
                                             THEN 1 ELSE 0 END) > 0,
                                    '[AddressID],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([AddressLine1] LIKE '%898)') _
                                             THEN 1 ELSE 0 END) > 0,
                                    '[AddressLine1],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([AddressLine2] LIKE '%898)') _
                                    THEN 1 ELSE 0 END) > 0,
                                    '[AddressLine2],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([City] LIKE '%898)') _
                                    THEN 1 ELSE 0 END) > 0, '[City],', NULL),
                                IIF(SUM(CASE WHEN ([StateProvinceID] _
                                    LIKE '%898)') THEN 1 ELSE 0 END) > 0,
                                    '[StateProvinceID],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([PostalCode] LIKE '%898)') _
                                     THEN 1 ELSE 0 END) > 0,
                                    '[PostalCode],',
                                    NULL),
                                IIF(
                                    SUM(   CASE
                                               WHEN ([SpatialLocation].ToString() _
                                               LIKE N'%898)' _
                                               COLLATE DATABASE_DEFAULT) THEN
                                                   1
                                               ELSE
                                                   0
                                           END
                                       ) > 0,
                                    '[SpatialLocation],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([rowguid] LIKE '%898)') _
                                THEN 1 ELSE 0 END) > 0, '[rowguid],', NULL),
                                IIF(
                                    SUM(   CASE
                                               WHEN (CONVERT(nvarchar(50), _
                                               [ModifiedDate], 126) LIKE '%898)') THEN
                                                   1
                                               ELSE
                                                   0
                                           END
                                       ) > 0,
                                    '[ModifiedDate],',
                                    NULL),
                                '[???]'
                            ) COLLATE DATABASE_DEFAULT,
                      N',[???]',
                      N''
                  ),
           REPLACE(
                      CONCAT(
                                IIF(SUM(CASE WHEN ([AddressID] LIKE '%898)') _
                                    THEN 1 ELSE 0 END) > 0,
                                    '[AddressID] AS [AddressID],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([AddressLine1] LIKE '%898)') _
                                    THEN 1 ELSE 0 END) > 0,
                                    '[AddressLine1] AS [AddressLine1],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([AddressLine2] LIKE '%898)') _
                                    THEN 1 ELSE 0 END) > 0,
                                    '[AddressLine2] AS [AddressLine2],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([City] LIKE '%898)') _
                                    THEN 1 ELSE 0 END) > 0,
                                    '[City] AS [City],',
                                    NULL),
                                IIF(
                                    SUM(   CASE
                                               WHEN ([StateProvinceID] LIKE '%898)') THEN
                                                   1
                                               ELSE
                                                   0
                                           END
                                       ) > 0,
                                    '[StateProvinceID] AS [StateProvinceID],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([PostalCode] LIKE '%898)') _
                                    THEN 1 ELSE 0 END) > 0,
                                    '[PostalCode] AS [PostalCode],',
                                    NULL),
                                IIF(
                                    SUM(   CASE
                                               WHEN ([SpatialLocation].ToString() _
                                               LIKE N'%898)' _
                                               COLLATE DATABASE_DEFAULT) THEN
                                                   1
                                               ELSE
                                                   0
                                           END
                                       ) > 0,
                                    '[SpatialLocation].ToString() _
                                     AS [SpatialLocation],',
                                    NULL),
                                IIF(SUM(CASE WHEN ([rowguid] LIKE '%898)') _
                                    THEN 1 ELSE 0 END) > 0,
                                    '[rowguid] AS [rowguid],',
                                    NULL),
                                IIF(
                                    SUM(   CASE
                                               WHEN (CONVERT(nvarchar(50), _
                                               [ModifiedDate], 126) _
                                               LIKE '%898)') THEN
                                                   1
                                               ELSE
                                                   0
                                           END
                                       ) > 0,
                                    'CONVERT(nvarchar(50),[ModifiedDate],126) _
                                     AS [ModifiedDate],',
                                    NULL),
                                '[???]'
                            ) COLLATE DATABASE_DEFAULT,
                      N',[???]',
                      N''
                  ),
           'SELECT [??] FROM [AdventureWorks2022].[Person].[Address] _
            WHERE ([AddressID] LIKE ''%898)'') OR ([AddressLine1] _
            LIKE ''%898)'') OR ([AddressLine2] LIKE ''%898)'') _
            OR ([City] LIKE ''%898)'') OR ([StateProvinceID] LIKE ''%898)'') _
            OR ([PostalCode] LIKE ''%898)'') OR ([SpatialLocation].ToString() _
            LIKE N''%898)'' COLLATE DATABASE_DEFAULT) OR ([rowguid] _
            LIKE ''%898)'') OR (CONVERT(nvarchar(50),[ModifiedDate],126) _
            LIKE ''%898)'') '
    FROM [AdventureWorks2022].[Person].[Address]
    WHERE ([AddressID] LIKE '%898)')
          OR ([AddressLine1] LIKE '%898)')
          OR ([AddressLine2] LIKE '%898)')
          OR ([City] LIKE '%898)')
          OR ([StateProvinceID] LIKE '%898)')
          OR ([PostalCode] LIKE '%898)')
          OR ([SpatialLocation].ToString() LIKE N'%898)' _
          COLLATE DATABASE_DEFAULT)
          OR ([rowguid] LIKE '%898)')
          OR (CONVERT(nvarchar(50), [ModifiedDate], 126) LIKE '%898)')

Basically, before the INSERT statement, we have a IF EXISTS statement which through a query checks whether any of the filtered columns matches the @valuePattern parameter using a standard SQL LIKE clauses and OR conditions. The INSERT statement is similar to one of the previous queries, the only changes are:

MatchingColumns (MatchingWhereColumns was null): The first difference is that we also have a FROM and WHERE clause to test if the filtered columns match the @valuePattern parameter and this implies that multiple matching rows can be returned and so some sort of aggregate function must be performed to return just a single string. I used a CASE condition which returns 1 or 0 if a column matches the @valuePattern parameter and then we aggregate all these values through the SUM operator, so basically the query returns the n value if the column under examination matches @valuePattern in n rows. Finally, we convert this number into a string through an IIF operator which returns the column name followed by comma if the SUM is greater than 0 (e.g., there is at least one match), NULL otherwise. Again, the REPLACE / CONCAT hack is used here to remove the last comma.

MatchingSelect this time the column is not null, but it contains the IF EXISTS subquery in order to allow the user to take it from the output table result and issue it in SSMS to retrieve quickly the matching columns and rows for that particular table.

As a final note, I point out that some kind of conversion has to be done for particular column datatypes in order to be used with the LIKE operator:

image --> CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY(MAX),@columnName),1)
xml --> CONVERT(nvarchar(MAX),@columnName)
hierarchyid -->@columnName.ToString()
geometry or geography --> These two column types behave differently since beside the column they change also the where condition which is one of these two:
- @columnName.STContains(geography::Parse(@valuePattern)) if @valuePattern represents a valid WKT and STContains method is supported, e.g., database compatibility_level is >= 130 [Sql Server 2016]
- @columnName.ToString() LIKE @valuePattern in all other cases
datetime, datetime2, datetimeoffset, time --> CONVERT(nvarchar(50),@columnName,126) [this is ISO8601 format so 'yyyy-mm-ddThh:mi:ss.mmm']

Here follows a sample table output:

spSearchTables logs for troubleshooting in the Messages tab in SSMS the general script at the beginning followed by all the queries issued for every table in order to populate the output one.

I tested the code on three well known SQL Server databases provided by Microsoft (Northwind, AdventureWorks2022 and pubs) and everything worked flawlessly, so hopefully this query should support a great deal of databases and almost any kind of column datatype (if it is not, please let me know by email). Probably, I will add some form of unit testing in the future, when I have spare time.

Points of Interest

I believe that extracting a comma separated list of matching columns from the rows of matching query is very witty (though a little bit hacky), so please carefully read the part about how MatchingColumns output string is created.

History

V1.0 (26^th October, 2023)
- Initial version
V1.1 (31^st October, 2023)
- Added @schemaSearchPattern and @columnTypeSearchPattern parameters
- Bugfix on geometry column type, WKT can be used in @valuePattern to perform STContains query on spatial columns when supported (e.g., database compatibility level >=130)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Federico Di Marco

Software Developer (Senior)

Italy

I'm a senior .NET software engineer in the second half of my forties. Started using a computer at six years, gone through logo, basic, assembly, C/C++, java and finally to .NET and .NET core. Proficient also in databases, especially Sql Server and reporting. Let's say I have also some experience on security but mainly in the past, now things have become much more difficult and I do not have too much time to keep me updated, but sometimes I am still kicking in. Fan of videogames, technologies, motorbikes, travelling and comedy.

Email: Federico Di Marco <fededim@gmail.com>
Linkedin: LinkedIn
Github: GitHub
Stackoverflow: StackOverflow

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.