Find a guid in a SQL Server database

Recently I got an error such as the following:

We suspect that there is a problem with the database. Unfortunately, it contains several dozen tables and we have little information on where to find this GUID…

As I did not want to look table by table, I preferred to send my investigator:

This one comes out of his hat a little SQL query and a few seconds after the result comes out:

We obtain the name of the table and the column that contains the value, as well as the ready SQL query to select the desired line if necessary 😃

No more suspense, here is the request:

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 seem barbaric but if it breaks down, we realize that it is ultimately rather simple.

#Explanation of the query

##Step 1: Select all uniqueidentifier columns

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 create a query such that:

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

This is actually the query generated by the SELECT and all QUOTENAME simplified version of course. The Common Table Expression (CTE) creates a query list as above

##Step 2: Concatenate queries with UNION ALL

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

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:

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 indicating PATH('') we indicate not to add the XML tag. Since there is only one column per line, the result does not contain any XML tags and therefore corresponds to the concatenation of the lines.

However, there remains a small problem to solve: there is a UNION ALL in excess at the beginning of the concatenated string. There are 2 solutions:

1. Prefix the query with a SELECT doing nothing:

   SQLcopy

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

2. Remove the UNION ALL from the string by using the STUFF function. This function allows you to replace part of a string with another. In our case we want to replace the first 11 characters (length of UNION ALL) by nothing (empty string):

   SQLcopy

   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:

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

There you go!

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