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.

Figurehead

Ah, SQL 2000. Missed that. Will revise. – Adalbertoadalheid

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

Gobioid

You can't defer the entire execution, because the query string will exceed 8000 characters before too long. But i can adopt your "tack on ORs until the table name changes. i'' updated your post with my final form, and mark you as answer. – Figurehead

Whoops, I was thinking @szQuery could just be varchar(max) to avoid this, but that's an MSSQL 2005 feature, so execution as-you-go is definitely safer. – Gobioid

What about when tables have shemas? – Servia

In order to remove blank outputs, I would recommend changing the EXEC statements to this: EXEC ('IF EXISTS (' + @szQuery + ') BEGIN ' + @szQuery + ' END'); – Toe

Changing line 53 as ` 'FROM ['+@tableName+'] '+` will eliminate the error you encountered when your ex colleague named a table as "User" -_- – Genuine

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.

Tati

BTW you need to take SCHEMA into account for table names and use the QUOTENAME – Tati

You also need SQL Server 2005. – Figurehead

Would be nice if that also gave you the column where the value found – Vireo

Answer 3

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

Adalbertoadalheid

Big thanks. Solution for MS SQL 2005 worked in MS SQL 2008. – Prodigal

Answer 4

I found the top rated script to be a bit slow, so I put together this one. It's faster for my use, and it returns a row count also. Enjoy.

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

Gramineous

Answer 5

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.

Ganglion

Answer 6

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.

Sears

Depends on how big the database is. I'm not sure how big the OP's database is, but for my use this would've been impractical. Just a partial export of a single table as text was 10GB, I would imagine the whole thing as a text export would be well over 100 GB. – Vainglory

I don't think using Notepad++ is even relevant here. Otherwise it's the answer for every sql query one can possibly imagine! Just search and replace. – Olag