Every now and then an error with only a GUID as part of the information comes up on a screen... The question is, does it live in my database and if so, where (and how) to find it? Today was one of those days and I quickly wrote a query to find out where the particular uniqueidentifier sits in de SQL Server database.
A quickly written query did the job, but I had this feeling it was suboptimal. The way I wrote it, required three scans or seeks on the source table. You’ll hardly notice the difference in AdventureWorks, but the faster (and resource-friendlier) the result is available, the better.
Edit 2020-11-24: the dynamic update statement was missing a few QUOTENAME functions in case object names needed quotes.
DECLARE @guid uniqueidentifier SELECT @guid = 'E552F657-A9AF-4A7D-A645-C429D6E02491' SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name , OBJECT_NAME(c.object_id) AS table_name , c.name AS column_name , @guid AS value , CAST (NULL AS bigint) AS value_count , CAST (NULL AS bigint) AS notnull_count , CAST (NULL AS bigint) AS row_count , CAST (NULL AS nvarchar(max)) AS preview_query INTO ##hunt_for_guid FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id = t.system_type_id INNER JOIN sys.objects o ON c.object_id = o.object_id WHERE t.name = 'uniqueidentifier' AND o.type = 'U' WHILE (SELECT COUNT(*) FROM ##hunt_for_guid WHERE row_count IS NULL) > 0 BEGIN DECLARE @sql nvarchar(max) /* -- Not optimal, requires reading the source table/index 3 times SELECT @sql = 'UPDATE ##hunt_for_guid SET value_count = (SELECT COUNT(' + QUOTENAME(column_name) + ') FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ' WHERE ' + QUOTENAME(column_name) + ' = ''' + CAST(@guid AS nvarchar(36)) + ''') , notnull_count = (SELECT COUNT(' + QUOTENAME(column_name) + ') FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ') , row_count = (SELECT COUNT(*) FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ') , preview_query = ''SELECT * FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ' WHERE ' + QUOTENAME(column_name) + ' = ''''' + + CAST(@guid AS nvarchar(36)) + ''''''' WHERE [schema_name] = ''' + schema_name + ''' AND [table_name] = ''' + table_name + ''' AND [column_name] = ''' + column_name + '''' FROM ##hunt_for_guid WHERE row_count IS NULL */ -- Optimized by scanning the source table/index only once. SELECT @sql = 'UPDATE H SET H.value_count = S.value_count, H.notnull_count = S.notnull_count, H.row_count = S.row_count , H.preview_query = ''SELECT * FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ' WHERE ' + QUOTENAME(column_name) + ' = ''''' + CAST(@guid AS nvarchar(36)) + ''''''' FROM ##hunt_for_guid AS H INNER JOIN (SELECT SUM(CASE WHEN ' + QUOTENAME (column_name) + ' = ''' + CAST(@guid AS nvarchar(36)) + ''' THEN 1 ELSE 0 END) AS value_count, COUNT(' + QUOTENAME(column_name) + ') AS notnull_count, COUNT(*) AS row_count FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ') AS S ON H.schema_name = ''' + schema_name + ''' AND H.table_name = ''' + table_name + ''' AND H.column_name = ''' + column_name + '''' FROM ##hunt_for_guid WHERE row_count IS NULL EXEC (@sql) END SELECT * FROM ##hunt_for_guid ORDER BY value_count DESC, schema_name, table_name, column_name DROP TABLE ##hunt_for_guid