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