Find a guid in a SQL Server database

 
 
  • Gérald Barré

Recently I got an error such as the following:

We suspected a problem in the database. Unfortunately, it contained several dozen tables and we had little information about where this GUID might be stored…

Rather than checking table by table, I decided to let my investigator handle it:

He pulled a SQL query out of his hat, and a few seconds later, the result was ready:

The result includes the table name, the column containing the value, and a ready-to-use SQL query to retrieve the matching row.

Without further ado, here is the query:

SQL
DECLARE @searchValue uniqueidentifier = 'a2843a1e-6ed4-4045-a179-51f0743943b8'
DECLARE @sql NVARCHAR(MAX);
WITH cte_sql_queries(sql_query) AS (
   SELECT 'SELECT ''' + QUOTENAME(t.TABLE_SCHEMA) + ''' schema_name '
   + '   , ''' + QUOTENAME(t.TABLE_NAME) + ''' table_name '
   + '   , ''' + QUOTENAME(c.COLUMN_NAME) + ''' column_name '
   + '   , ''SELECT ' + QUOTENAME(c.COLUMN_NAME) + ', * FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' WHERE ' +  QUOTENAME(c.COLUMN_NAME) + '='''''+ CAST(@searchValue AS NVARCHAR(36)) +''''''' query '
   + ' FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)
   + ' WHERE ' +  QUOTENAME(c.COLUMN_NAME) + '=@searchValue'
   FROM INFORMATION_SCHEMA.Columns c
   INNER JOIN INFORMATION_SCHEMA.Tables t
   ON t.TABLE_NAME = c.TABLE_NAME
   AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
   AND t.TABLE_TYPE = 'BASE TABLE'
   AND c.DATA_TYPE = 'uniqueidentifier'
)
SELECT @sql = STUFF((SELECT ' UNION ALL ' + sql_query FROM cte_sql_queries FOR XML PATH('')), 1, LEN(' UNION ALL '), '')
exec sp_executesql @sql, N'@searchValue uniqueidentifier', @searchValue;

The query may look complex at first, but once broken down, it is actually quite simple.

#Explanation of the query

##Step 1: Select all uniqueidentifier columns

SQL
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
    ON t.TABLE_NAME = c.TABLE_NAME
    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_TYPE = 'BASE TABLE' -- Select only tables (not views)
    AND c.DATA_TYPE = 'uniqueidentifier'

By concatenating these values, we can build a query like:

SQL
SELECT '[dbo].[Customer].[Customer_Id]' FROM [dbo].[Customer] WHERE [Customer_Id] = @searchValue

This is essentially the query generated by the SELECT with all the QUOTENAME calls, simplified for clarity. The Common Table Expression (CTE) builds a list of such queries:

##Step 2: Concatenate queries with UNION ALL

The easiest way to concatenate all the rows is to use FOR XML. This instruction converts the output to XML:

SQL
SELECT ' UNION ALL ' + sql_query FROM cte_sql_queries FOR XML PATH('')

PATH allows you to specify the name of the XML tag enclosing the XML of a line. Here're some examples:

SQL
SELECT 'John' as FirstName, 'Doe' as LastName FOR XML PATH
-- <row><FirstName>John</FirstName><LastName>Doe</LastName></row>

SELECT 'John' as FirstName, 'Doe' as LastName FOR XML PATH('sample')
-- <sample><FirstName>John</FirstName><LastName>Doe</LastName></sample>

SELECT 'John' as FirstName, 'Doe' as LastName FOR XML PATH('')
-- <FirstName>John</FirstName><LastName>Doe</LastName>

By specifying PATH(''), we tell SQL Server not to wrap values in XML tags. Since there is only one column per row, the result contains no XML tags and is simply the concatenation of all rows.

However, there is one small issue: there is an extra UNION ALL at the beginning of the concatenated string. There are two solutions:

  1. Prefix the query with a no-op SELECT:

    SQL
    'SELECT NULL, NULL, NULL, NULL  WHERE 0=1' + (SELECT ... FOR XML PATH(''))

  2. Remove the extra UNION ALL using the STUFF function, which replaces part of a string with another string. Here, we replace the first 11 characters (the length of UNION ALL) with an empty string:

    SQL
    STUFF((SELECT ' UNION ALL ' + sql_query FROM cte_sql_queries FOR XML PATH('')), 1, LEN(' UNION ALL '), '')

##Step 3: Run the dynamically built query

We now have the SQL query to execute in a string, so just run it:

SQL
exec sp_executesql @sql, N'@searchValue uniqueidentifier', @searchValue;

There you go!

Do you have a question or a suggestion about this post? Contact me!

Follow me:
Enjoy this blog?