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.

Solution

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 @sql NVARCHAR(MAX) = ''
DECLARE @one_sql NVARCHAR(1000)

DECLARE tables_cursor CURSOR
    FOR SELECT [name]
    FROM sys.objects O
    WHERE type = 'U'
    AND EXISTS (
        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
WHILE @@FETCH_STATUS = 0
BEGIN
    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
    ELSE
        SQL @sql = @sql + ' UNION ' + @one_sql
    FETCH NEXT FROM tables_cursor INTO @table_name
END
CLOSE tables_cursor
DEALLOCATE tables_cursor

EXEC sp_executesql @sql