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.
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:
USE [?]
DECLARE @dbName nvarchar(200), @schemaName nvarchar(200),_
@tableName nvarchar(200), @columnName nvarchar(200), @columnType nvarchar(200), _
@fullTableName nvarchar(1000)
DECLARE @oldDbName nvarchar(200), @oldSchemaName nvarchar(200),_
@oldTableName nvarchar(200), @oldFullTableName nvarchar(1000)
DECLARE @whereColumnName nvarchar(200), @whereCondition nvarchar(400), _
@whereClause nvarchar(max), @sql nvarchar(max), @selectSql nvarchar(max), _
@columnListSelect nvarchar(max), @columnList nvarchar(max)
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'')
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'''')'
SET @columnList = N'REPLACE(CONCAT('+@columnList + N'''[???]'') _
COLLATE DATABASE_DEFAULT,N'',[???]'',N'''')'
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'''')'
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.
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:
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:
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 (26th October, 2023)
- V1.1 (31st 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)
