Find the SQL Server GUID table

#sql #databases #debugging #sql-server

Written by Anders Marzi Tornblad

Sometimes, when debugging a complex solution, you find yourself with an ID, but you're not sure what database table it belongs to. This is especially common if your service is based on some complex framework that passes IDs around without any clear information about the table or entity class.

Depending on how your database is set up, finding the table can be easy or tricky, so I thought I'd write a series of articles for different scenarios. First up is Microsoft SQL Server (also applicable to Azure SQL) with UNIQUEIDENTIFIER columns (or GUID) for storing IDs.


If your tables use a UNIQUEIDENTIFIER column called ID for the primary key, you are probably in luck, because that data type is globally unique across all tables. This script will help you find the table based on a globally unique ID:

DECLARE @id UNIQUEIDENTIFIER = '01234567-89ab-cdef-fedc-ba9876543210' -- CHANGE THIS

DECLARE @table_name NVARCHAR(100)
DECLARE @one_sql NVARCHAR(1000)

DECLARE tables_cursor CURSOR
    FOR SELECT [name]
    FROM sys.objects O
    WHERE type = 'U'
        SELECT 1
        FROM sys.columns C
        WHERE C.[name] = 'Id'
        AND C.object_id = O.object_id

OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @table_name
    SET @one_sql = 'SELECT ''' + @table_name + ''' AS [table] FROM [' + @table_name + '] ' +
                   'WHERE ID = ''' + CONVERT(CHAR(36), @id) + ''''
    IF LEN(@sql) = 0
        SET @sql = @one_sql
        SQL @sql = @sql + ' UNION ' + @one_sql
    FETCH NEXT FROM tables_cursor INTO @table_name
CLOSE tables_cursor
DEALLOCATE tables_cursor

EXEC sp_executesql @sql