SQL Server: поиск во всех таблицах определенного GUID

Question

Я столкнулся с необходимостью очистки некоторых данных, и мне нужно найти некоторые конкретные руководства (то есть уникальные идентификаторы) в SQL Server°.

я придумал хранимую процедуру, которая выполняет SELECT из каждого столбца uniqueidentifier в каждой таблице в текущей базе данных и возвращает набор результатов, если найден 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
Может ли кто-нибудь найти способ изменить его, чтобы выполнить поиск по нескольким столбцам уникальных идентификаторов в той же таблице, что и 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

Вы можете отложить EXEC, пока ваш цикл курсора не закончится. Затем просто отследите имя таблицы внутри цикла и, если оно совпадает, добавьте ИЛИ, в противном случае завершите свой 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, который выполняет UNION ALL из всех таблиц и полей uniqueidentifier. Что-то с такой схемой:

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
    )

Затем вам просто нужно выполнить один оператор SELECT для этого повторно используемого VIEW, чтобы получить все совпадающие 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 2

Вы можете свернуть все в один 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 3

Вот решение для 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 4

Я обнаружил, что сценарий с самым высоким рейтингом немного медленный, поэтому я собрал этот. Для меня это быстрее, а также возвращает количество строк. Наслаждаться.

      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

Answer 5

Похоже, что здесь происходит немного больше инженерии... Вы сказали, что вам просто нужно "найти каких-то конкретных гидов". Возможно, будет проще экспортировать всю базу данных, а затем открыть ее в notepad++ и найти нужные руководства. Затем вы увидите всю строку данных в то время и т. Д.

Вы можете прочитать о мастере публикации SQL Server, который экспортирует базу данных в текстовый файл здесь.

Answer 6

Похоже, вы в основном хотите объединить список столбцов в ваш динамический SQL. В mssql нет функции concat первого класса, вы можете написать свой собственный CLR udf для этого, но мне не нравится это решение. Проверьте этот вопрос для некоторых решений mssql concat.