spSearchTables：按名称或值搜索和查找表或列

最新推荐文章于 2024-06-20 13:14:51 发布

寒冰屋

最新推荐文章于 2024-06-20 13:14:51 发布

阅读量9

点赞数

分类专栏：数据库文章标签：数据库 mssql server spSearchTables

原文链接：https://www.codeproject.com/Articles/5370606/spSearchTables-Search-and-Find-Tables-or-Columns-b

版权

数据库专栏收录该内容

215 篇文章 12 订阅

订阅专栏

介绍

我一直担任顾问，每1到3年更换一次项目。每次切换一个项目时，你都必须处理新的数据库，这些数据库很少是没有记录的。我通常处理中等大小的数据库（少于300个表），也听说过同事处理更大的数据库（大约700个表），但最近我加入了一个庞大的新项目（几个数据库，其中一些包含2000个表的惊人大小！），试图理解它们有时非常麻烦。出于这个原因，我决定编写这个帮助程序存储过程，它允许您使用LIKE通配符语法搜索数据库、表、列或列数据。

背景

使用该代码是一件轻而易举的事，只需将存储过程代码粘贴到SSMS中，然后使用所需的搜索选项调用它即可。存储过程的T-SQL代码反而复杂且棘手，但我认为任何对T-SQL有中等了解的人都能够理解它。

使用代码

您可以查看我的GitHub存储库，以获取有关如何使用spSearchTables。以下是实现的主要概念：

该代码输出一个包含以下列的表：
- [Database]：数据库名称匹配@dbSearchPattern参数
- [Schema]：与表关联的架构名称
- [Table]：匹配@tableSearchPattern参数的表名
- [FullTableName]：这只是数据库+架构+表的串联
- [MatchingColumns]：当@columnsSearchPattern和@valuePatternnull不为空时，用逗号分隔的列名列表
- [MatchingWhereColumns]：辅助列，它未显示在最终输出中，它类似于MatchingColumns，但它显示了用于LIKE模式匹配的转换后的列语句（见下文）。
- [MatchingSelect]：select语句在@valuePattern不为null时返回与之匹配的行(它支持所有列数据类型)。匹配的列首先显示在输出中。
spSearchTables通过对sys.databases catalog执行以下查询，在检索到所有数据库的列表后循环遍历游标。

SELECT [name]
FROM sys.databases

它不使用Microsoft sp_MSforeachdb，因为它具有支持最多2000个字符的脚本的限制（此SP生成的脚本更长，最初我使用sp_MSforeachdb，但是在花了将近2个小时来了解为什么会发生奇怪的错误后，我发现输入脚本在2K字符处🤬被截断）。

对于每个匹配的数据库，它执行此通用脚本，向下传递4个输入参数，@valuePattern, @columnSearchPattern, @columnTypeSearchPattern和@schemaSearchPattern：

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];

上面的通用脚本在循环中选择外部数据库，并再次查询系统目录，了解其表、架构、列以及所有这些表的数据类型和周期。

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

然后，代码变得非常复杂和棘手，但基本上，它构建并执行以下两个查询之一，这些查询使用有关数据库中找到的筛选表/列的信息填充输出表。

- @valuePattern参数为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基本上是一个可变长度的CONCAT，所有列名加上逗号加上一个额外的自定义后缀（'[???]'希望它不应该与任何表名匹配）。后缀用于从最后一列中删除最后一个逗号（字符串',[???]'将通过REPLACE函数替换为空的string）。这是一个hack，它避免了执行内部子查询来检索string长度，以便用SUBSTRING修剪最后一个逗号。

MatchingWhereColumns/MatchingSelectNULL参数是因为我们不查询任何值。

- @valuePattern参数不为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)')

基本上，在INSERT语句之前，我们有一个IF EXISTS语句，它通过查询检查是否有任何过滤的列与使用标准SQL LIKE子句和OR条件的@valuePattern参数匹配。该INSERT语句与前面的查询之一类似，唯一的更改是：

MatchingColumns (MatchingWhereColumns是null)：第一个区别是，我们还有一个FROM和WHERE子句来测试过滤后的列是否与@valuePattern参数匹配，这意味着可以返回多个匹配的行，因此必须执行某种聚合函数才能只返回一个string。我使用了一个CASE条件，该条件返回1或0如果列与@valuePattern参数匹配，然后我们通过SUM运算符聚合所有这些值，因此，如果检查的列在n行中匹配@valuePattern，则查询基本上会返回该n值。最后，我们通过一个IIF运算符将这个数字转换为一个string，如果SUM大于0（例如，至少有一个匹配项），则返回列名后跟逗号，否则返回NULL。同样，这里使用REPLACE/CONCAT hack来删除最后一个逗号。

MatchingSelect这一次，该列不是null，但它包含IF EXISTS子查询，以便允许用户从输出表结果中获取它并在SSMS中发出它以快速检索该特定表的匹配列和行。

最后一点，我指出，必须对特定的列数据类型进行某种转换，以便与LIKE运算符一起使用：

image --> CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY(MAX),@columnName),1)
xml --> CONVERT(nvarchar(MAX),@columnName)
hierarchyid -->@columnName.ToString()
geometry或geography-->这两种列类型的行为不同，因为在列旁边，它们还会更改以下两种类型之一的where条件：
- @columnName.STContains(geography::Parse(@valuePattern)) 如果@valuePattern表示有效的WKT并且支持STContains方法，例如，数据库compatibility_level是>= 130 [Sql Server 2016]
- @columnName.ToString() LIKE @valuePattern在所有其他情况下
datetime、datetime2、datetimeoffset、time--> CONVERT(nvarchar(50),@columnName,126) [这是ISO8601格式，所以'yyyy-mm-ddThh:mi:ss.mmm']

下面是一个示例表输出：

spSearchTables用于在SSMS的“消息”选项卡中进行故障排除的日志，开头是常规脚本，然后是为每个表发出的所有查询，以便填充输出表。

我在Microsoft（Northwind，AdventureWorks2022和pubs ）提供的三个著名的SQL Server数据库上测试了代码，一切都完美无缺，所以希望这个查询应该支持大量的数据库和几乎任何类型的列数据类型（如果不是，请通过电子邮件告诉我）。也许，当我有空闲时间时，我会在未来添加某种形式的单元测试。