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

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 

Answer 2

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.