title: SQL Server: Search all tables for a particular GUID
body: <p>i came across the need to cleanse some data, and i need to find some particular guids (i.e. <strong>uniqueidentifiers</strong>) in SQL Server°.</p>
  
  <p>i've come up with a stored procedure that does a <strong>SELECT</strong> from every uniqueidentifier column in every table in the current database, and returns a result set if the guid is found.</p>
  
  <p>It uses the INFORMATION_SCHEMA views to find all <strong>uniqueidentifier</strong> columns in all <strong>base tables</strong> (as opposed to views). For each column it issues a select, returning the name of the table and the column where it was found.</p>
  
  <pre><code>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  
  </code></pre>
  
  <hr>
  
  <p>My question are:</p>
  
  <p><strong>Question 1</strong><br>
  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</p>
  
  <p>i.e.</p>
  
  <pre><code>SELECT ... FROM Prices WHERE BookGUID = '{...}'
  SELECT ... FROM Prices WHERE AuthorGUID = '{...}'
  SELECT ... FROM Prices WHERE PublisherGUID = '{...}'
  SELECT ... FROM Prices WHERE StoreGUID = '{...}'
  </code></pre>
  
  <p>would become:</p>
  
  <pre><code>SELECT ... 
  FROM Prices 
  WHERE BookGUID = '{...}'
  OR AuthorGUID = '{...}'
  OR PublisherGUID = '{...}'
  OR StoreGUID = '{...}'
  </code></pre>
  
  <p>i tried using a cursor inside a cursor, but the <strong>FETCH_STATUS</strong>'s conflict.</p>
  
  <p><strong>Question 2</strong>
  Can anyone think of any better way to do it?‡</p>
  
  <hr>
  
  <p>Footnotes:</p>
  
  <p>° SQL Server 2000</p>
  
  <p>‡ Subject to the constraint of using uniqueidentifiers in a relational database.</p>