sql-serversearchguiduniqueidentifier

SQL Server: Search all tables for a particular GUID


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.


Solution

  • 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 ('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