SQL Server：搜索所有表中的特定 GUID

Question

我遇到了清理一些数据的需要，并且我需要在 SQL Server° 中找到一些特定的 guid（即唯一标识符）。

我提出了一个存储过程，它从当前数据库中每个表中的每个 uniqueidentifier 列执行 SELECT 操作，如果找到 guid，则返回一个结果集。

它使用 INFORMATION_SCHEMA 视图查找所有基表中的所有uniqueidentifier列（而不是视图）。 对于每一列，它发出一个选择，返回表的名称和找到它的列。

CREATE PROCEDURE dbo.FindGUID @searchValue uniqueidentifier AS
/*
    Search all tables in the database for a guid

      6/9/2009: Removed the IF EXISTS to double hit the database
*/

--DECLARE @searchValue uniqueidentifier
--SET @searchValue = '{2A6814B9-8261-452D-A144-13264433864E}'

DECLARE abc CURSOR FOR
    SELECT 
        c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.Columns c
        INNER JOIN INFORMATION_SCHEMA.Tables t
        ON c.TABLE_NAME = t.TABLE_NAME
        AND t.TABLE_TYPE = 'BASE TABLE'
    WHERE DATA_TYPE = 'uniqueidentifier'

DECLARE @tableName varchar(200)
DECLARE @columnName varchar(200)
DECLARE @szQuery varchar(8000)

OPEN ABC

FETCH NEXT FROM abc INTO @tableName, @columnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @szQuery = 
        'SELECT '''+@tableName+''' AS TheTable, '''+@columnName+''' AS TheColumn '+
        'FROM '+@tableName+' '+
        'WHERE '+@columnName+' = '''+CAST(@searchValue AS varchar(50))+''''

    PRINT 'Searching '+@tableName+'.'+@columnName+'..'
    PRINT @szQuery
    EXEC (@szQuery)

    FETCH NEXT FROM abc INTO @tableName, @columnName
END

CLOSE abc
DEALLOCATE abc  

---

我的问题是：

问题 1
任何人都可以找到一种方法来更改它以在同一个表中执行多个 uniqueidentifier 列的搜索作为 OR，而不是单独的查询，

即

SELECT ... FROM Prices WHERE BookGUID = '{...}'
SELECT ... FROM Prices WHERE AuthorGUID = '{...}'
SELECT ... FROM Prices WHERE PublisherGUID = '{...}'
SELECT ... FROM Prices WHERE StoreGUID = '{...}'

会变成：

SELECT ... 
FROM Prices 
WHERE BookGUID = '{...}'
OR AuthorGUID = '{...}'
OR PublisherGUID = '{...}'
OR StoreGUID = '{...}'

我尝试在游标内使用游标，但是 FETCH_STATUS 的冲突。

问题2 谁能想到更好的方法来做到这一点？ ‡

---

脚注：

° SQL Server 2000

‡ 受到在关系数据库中使用唯一标识符的约束。

Answer 1

听起来您基本上想将列列表连接到动态 sql 中。 mssql 中没有一流的 concat 函数，您可以编写自己的 CLR udf 来执行此操作，但我不喜欢该解决方案。 检查 这个问题对于一些mssql concat解决方案。

Answer 2

看起来这里的工程有点过了……你说你只需要“找到一些特定的指导”。 导出整个数据库然后在记事本++中打开它并搜索您想要的指南可能会更容易。 然后您将看到当时的整行数据等。

您可以阅读有关将数据库导出到文本文件的 SQL Server 发布向导
这里。

Answer 3

您可以推迟执行，直到光标循环完成。 然后，只需跟踪循环内的表名称，如果相同，则添加一个 OR，否则结束 SELECT 并开始新的 SELECT。

DECLARE @lasttable varchar(255);
SET @lasttable='';
FETCH NEXT FROM abc INTO @tableName, @columnName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
   IF(@lasttable=@tablename) BEGIN
       SET @szQuery = @szQuery + ' OR [' + @columnName + ']=''' + CAST(@searchValue AS varchar(50)) + '''';
   END ELSE BEGIN
       SET @lasttable = @tablename;
       SET @szQuery = @szQuery + 
         'SELECT '''+@tableName+''' AS TheTable, '''+@columnName+''' AS TheColumn '+
         'FROM '+@tableName+' '+
         'WHERE '+@columnName+' = '''+CAST(@searchValue AS varchar(50))+''''
   END
   FETCH NEXT FROM abc INTO @tableName, @columnName;
END
PRINT @szQuery;
EXEC (@szQuery);

您还可以创建存储过程来构建一个 VIEW，该 VIEW 对所有表和 uniqueidentifier 字段执行 UNION ALL。 具有这样的架构：

CREATE VIEW all_uuids AS (
    SELECT 'prices' AS tablename, 'BookGUID' as fieldname, ID as primarykey, BookGUID AS guid FROM prices
    UNION ALL SELECT 'prices', 'AuthorGUID', ID, AuthorGUID FROM prices
    UNION ALL SELECT 'othertable', 'otherfield', ID, otherfield FROM othertable
    )

然后，您只需在这个可重用的 VIEW 上执行单个 SELECT 语句即可获取所有匹配的 GUID。 要在单个表中搜索，请使用相关子查询，例如：

SELECT * FROM prices WHERE EXISTS (SELECT null FROM all_uuids u WHERE u.primarykey=prices.id AND u.guid=@searchfor AND u.tablename='prices')

这将搜索价格表中的所有 GUID 字段。 SQL Server 足够智能，不会查找其他表，而是使用现有表的索引。

通过重用单个视图，您只需在更改架构时循环访问 information_schema，而不是每次查询，并且视图的结果比存储过程的结果更容易连接。

---

答案

原始海报最终解决方案，基于此答案：

CREATE PROCEDURE dbo.FindGUID @searchValue uniqueidentifier AS

/*
    Search all tables in the database for a guid

    Revision History
    6/9/2009: Initally created
    6/10/2009: Build or clause of multiple columns on one table
*/

--DECLARE @searchValue uniqueidentifier
--SET @searchValue = '{2A6814B9-8261-452D-A144-13264433864E}'

DECLARE abc CURSOR FOR
    SELECT 
        c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.Columns c
        INNER JOIN INFORMATION_SCHEMA.Tables t
        ON c.TABLE_NAME = t.TABLE_NAME
        AND t.TABLE_TYPE = 'BASE TABLE'
    WHERE DATA_TYPE = 'uniqueidentifier'

DECLARE @tableSchema varchar(200)
DECLARE @tableName varchar(200)
DECLARE @columnName varchar(200)
DECLARE @szQuery varchar(8000)
SET @szQuery = ''

DECLARE @lasttable varchar(255);
SET @lasttable='';

OPEN ABC

FETCH NEXT FROM abc INTO @tableSchema, @tableName, @columnName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
   IF(@lasttable=@tablename) 
   BEGIN
      SET @szQuery = @szQuery + ' OR [' + @columnName + ']=''' + CAST(@searchValue AS varchar(50)) + '''';
   END 
   ELSE 
   BEGIN
       SET @lasttable = @tablename;

       IF @szQuery <> '' 
       BEGIN
          PRINT @szQuery
          EXEC ('IF EXISTS (' + @szQuery + ') BEGIN ' + @szQuery + ' END');
       END

       SET @szQuery = 
         'SELECT '''+@tableSchema+'.'+@tableName+''' AS TheTable, '''+@columnName+''' AS TheColumn '+
         'FROM '+@tableName+' '+
         'WHERE '+@columnName+' = '''+CAST(@searchValue AS varchar(50))+''''
   END
   FETCH NEXT FROM abc INTO @tableSchema, @tableName, @columnName;
END

CLOSE abc
DEALLOCATE abc

IF @szQuery <> '' 
BEGIN
    PRINT @szQuery
    EXEC ('IF EXISTS (' + @szQuery + ') BEGIN ' + @szQuery + ' END');
END
GO

Answer 4

您可以将所有内容包装到一个 SELECT 中并立即搜索所有表：

ALTER PROCEDURE dbo.FindGUID @searchValue uniqueidentifier AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX);
WITH cte_all_tables(SQL) AS (
    SELECT N'SELECT ''' + QUOTENAME(t.TABLE_SCHEMA) + '.' +QUOTENAME(t.TABLE_NAME) + 
        + N''' FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' +QUOTENAME(t.TABLE_NAME)
        + N' WHERE ' +
        (
            SELECT QUOTENAME(c.COLUMN_NAME) + N'= @searchValue OR '
            FROM INFORMATION_SCHEMA.Columns c
            WHERE c.TABLE_NAME = t.TABLE_NAME
                AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
                AND c.DATA_TYPE = 'uniqueidentifier'
            FOR XML PATH('')
        ) + N' 0=1 ' 
   FROM INFORMATION_SCHEMA.Columns c
        INNER JOIN INFORMATION_SCHEMA.Tables t
        ON c.TABLE_NAME = t.TABLE_NAME
        AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND t.TABLE_TYPE = 'BASE TABLE'
    WHERE DATA_TYPE = 'uniqueidentifier')
SELECT @sql = (SELECT [SQL] + N' UNION ALL ' FROM cte_all_tables
FOR XML PATH('')) + N' SELECT NULL WHERE 0=1';
PRINT @SQL;
exec sp_executesql @sql, N'@searchValue uniqueidentifier', @searchValue;
END

我使用了像“OR 0=1”这样的逻辑删除终止符，甚至使用了整个 UNION，但这只是因为我太懒了，无法从构建的串联中修剪结尾字符串。

Answer 5

这是针对 SQL 2000 的解决方案，无偿使用游标：

declare @searchvalue uniqueidentifier
set @searchValue = '{2A6814B9-8261-452D-A144-13264433864E}'

if object_id('tempdb..#results') is not null drop table #results
create table #results (TableSchema sysname, TableName sysname)

declare @sql nvarchar(4000)

declare @cursor1 cursor
declare @tablename sysname
declare @tableschema sysname

declare @cursor2 cursor
declare @columnname sysname
declare @searchFields nvarchar(4000)

set @cursor1 = cursor for
  select t.TABLE_SCHEMA, t.TABLE_NAME
  from INFORMATION_SCHEMA.Tables t
  where t.TABLE_TYPE = 'BASE TABLE'
    and exists (
      select * from INFORMATION_SCHEMA.Columns c
      where c.TABLE_NAME = t.TABLE_NAME
        and c.TABLE_SCHEMA = t.TABLE_SCHEMA
        and c.DATA_TYPE = 'uniqueidentifier'
      )

open @cursor1
while 1=1 begin
  fetch next from @cursor1 into @tableschema, @tablename
  if @@fetch_status <> 0 break

  set @searchFields = ''
  set @cursor2 = cursor for 
    select c.COLUMN_NAME
    from INFORMATION_SCHEMA.Columns c
    where c.TABLE_NAME = @tablename
      and c.TABLE_SCHEMA = @tableschema
      and c.DATA_TYPE = 'uniqueidentifier'

  open @cursor2
  while 1=1 begin
    fetch next from @cursor2 into @columnname
    if @@fetch_status <> 0 break
    set @searchFields = @searchFields + ', ' + quotename(@columnname)
  end      

  set @searchFields = substring(@searchFields,3,len(@searchFields))
  set @sql = ' insert #results'
           + ' select '''+@tableschema+''','''+@tablename+''''
           + ' from '+quotename(@tableschema)+'.'+quotename(@tablename)
           + ' where @searchValue in ('+@searchFields+')'

  print @sql
  exec sp_executesql @sql, N'@searchValue uniqueidentifier', @searchValue
end

select * from #results

这是针对 SQL 2005 的解决方案，基于 Remus 的解决方案，并使用临时表来实现更好的扩展：

DECLARE @searchValue uniqueidentifier
SET @searchValue = '{2A6814B9-8261-452D-A144-13264433864E}'

IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results
CREATE TABLE #results (TableSchema SYSNAME, TableName SYSNAME);
DECLARE @sql NVARCHAR(MAX);
WITH cte_all_tables(SQL) AS (
    SELECT
          N' INSERT #results (TableSchema, TableName)'
        + N' SELECT ''' + t.TABLE_SCHEMA + ''', ''' + t.TABLE_NAME + N'''' 
        + N' FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' +QUOTENAME(t.TABLE_NAME)
        + N' WHERE ' +
        (
                SELECT QUOTENAME(c.COLUMN_NAME) + N' = @searchValue OR '
                FROM INFORMATION_SCHEMA.Columns c
                WHERE c.TABLE_NAME = t.TABLE_NAME
                        AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
                        AND c.DATA_TYPE = 'uniqueidentifier'
                FOR XML PATH('')
        ) + N'0=1'
   FROM INFORMATION_SCHEMA.Columns c
        INNER JOIN INFORMATION_SCHEMA.Tables t
        ON c.TABLE_NAME = t.TABLE_NAME
        AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
        AND t.TABLE_TYPE = 'BASE TABLE'
    WHERE DATA_TYPE = 'uniqueidentifier')
SELECT @sql = (SELECT [SQL]+nchar(10) FROM cte_all_tables FOR XML PATH(''));

PRINT @SQL;
exec sp_executesql @sql, N'@searchValue uniqueidentifier', @searchValue;
SELECT * FROM #results

Answer 6

我发现评分最高的脚本有点慢，所以我把这个放在一起。 它对我来说速度更快，并且还返回行数。 享受。

DECLARE 
    @GuidToSearch uniqueidentifier = '23D91E70-245F-42FE-9587-882EA9D52432';

DROP TABLE IF EXISTS #GuidSearchResult
CREATE TABLE #GuidSearchResult
(
    SchemaName nvarchar(300) NOT NULL,
    TableName  nvarchar(300) NOT NULL,
    ColumnName nvarchar(300) NOT NULL,
    NoOfRows   int           NOT NULL
);

DECLARE
    @Schema nvarchar(300),
    @Table nvarchar(300),
    @Column nvarchar(300),
    @Script nvarchar(MAX),
    @ScriptTemplate  nvarchar(MAX) = CONCAT(
        'INSERT #GuidSearchResult SELECT ''#SCHEMA#'', ''#TABLE#'', ''#COLUMN#'', COUNT(*) FROM #SCHEMA#.[#TABLE#] WHERE #COLUMN# = ''',@GuidToSearch,''' HAVING COUNT(*) > 0');

DECLARE cur CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
    SELECT
        t.TABLE_SCHEMA,
        t.TABLE_NAME,
        c.COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.TABLES t
        JOIN INFORMATION_SCHEMA.COLUMNS c ON 
            t.TABLE_NAME = c.TABLE_NAME AND 
            t.TABLE_SCHEMA = c.TABLE_SCHEMA
    WHERE
        c.DATA_TYPE = 'uniqueidentifier' AND 
        t.TABLE_TYPE = 'BASE TABLE';
OPEN cur;

WHILE 1 = 1
BEGIN
    FETCH FROM cur INTO @Schema, @Table, @Column;
    IF @@FETCH_STATUS <> 0
        BREAK;

    SELECT
        @Script = REPLACE(REPLACE(REPLACE(@ScriptTemplate, 
                    '#SCHEMA#', @Schema),
                    '#TABLE#', @Table),
                    '#COLUMN#', @Column)

    PRINT @Script
    EXEC(@Script)
END;

CLOSE cur;
DEALLOCATE cur;

-- Return result
SELECT
    SR.SchemaName,
    SR.TableName,
    SR.ColumnName,
    SR.NoOfRows
FROM
    #GuidSearchResult SR