SQL Server: Search all tables for a particular GUID

Question

i came across the need to cleanse some data, and i need to find some particular guids (i.e. uniqueidentifiers) in SQL Server°.

i've come up with a stored procedure that does a SELECT from every uniqueidentifier column in every table in the current database, and returns a result set if the guid is found.

It uses the INFORMATION_SCHEMA views to find all uniqueidentifier columns in all base tables (as opposed to views). For each column it issues a select, returning the name of the table and the column where it was found.

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

---

My question are:

Question 1
Could anyone figure out a way to change it to perform a search of multiple uniqueidentifier columns in the same table as ORs, rather than separate queries

i.e.

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

would become:

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

i tried using a cursor inside a cursor, but the FETCH_STATUS's conflict.

Question 2 Can anyone think of any better way to do it?‡

---

Footnotes:

° SQL Server 2000

‡ Subject to the constraint of using uniqueidentifiers in a relational database.

Answer 1

Sounds like you basically want to concatenate the list of columns into your dynamic sql. There isn't a first class concat function in mssql, you can write your own CLR udf to do it but I don't love that solution. Check this question for some mssql concat solutions.

Answer 2

You could defer the EXEC until your cursor loop is done. Then, just track the table name inside your loop and if it's the same, add an OR, otherwise end your SELECT and start a new one.

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

You could also create the stored procedure to build a VIEW that does a UNION ALL of all tables and uniqueidentifier fields. Something with a schema like this:

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
    )

Then, you just need to perform a single SELECT statement on this reusable VIEW to get all of the matching GUIDs. To search within a single table, use a correlated subquery, e.g.:

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

That will search across all GUID fields in the prices table. SQL Server is smart enough to not go looking through other tables, and it uses your existing tables' indexes.

By re-using a single view, you only have to go looping through information_schema when you change your schema, not with every query, and the results of a view can be joined more readily than the results of a stored procedure.

---

Answer

Original posters final solution, based on this answer:

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 (@szQuery);
       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 (@szQuery);
END
GO

Answer 3

You can wrap all into a single SELECT and search all tables at once:

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

I used tombstone terminators like 'OR 0=1' and even an entire UNION, but that is just because I'm too lazy to trim the ending from the built concatenated strings.

Answer 4

Here's a solution for SQL 2000, with gratuitous use of cursors:

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

Here's a solution for SQL 2005, based on Remus's solution, with temp tables for better scaling:

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 5

Looks like a little over engineering going on here.... You said you just needed to "find some particular guids". It might be easier to export the whole database and then open it up in notepad++ and search for the guids you wanted. Then you will be seeing the whole row of data at that time, etc.

You can read about the SQL Server Publishing Wizard that exports the database to a text file here.