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.
Answer
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