Extract Relationships and description

Question

Hi Community,

Greetings!

I am exploring on the lake database templates in Azure synapse analytics to understand dynamics mapping. I am able to fetch the table, column and data type info with the information schema and sys tables, however I can see there are descriptions to each tables and relationships, I have around 400 plus tables and if i want to change or add tables, I am not able to capture relationship info. Is there any metadata script or table that holds this information.

Reference script:

SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE
FROM 
    INFORMATION_SCHEMA.TABLES t
JOIN 
    INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE 
    t.TABLE_TYPE = 'BASE TABLE'
ORDER BY 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    c.ORDINAL_POSITION;

Appreciate your help in advance!

Thanks and regards,

Hrishi

Answer 1

@Hrishikesh K M It sounds like you're trying to get a comprehensive view of the metadata associated with your tables in Azure Synapse Analytics, especially the relationships and descriptions that aren't captured in the INFORMATION_SCHEMA views.

For relationships between tables, you won’t find this data directly in the INFORMATION_SCHEMA. Instead, you may need to look into database diagrams or a specific metadata table if your setup supports it.

Please consider below steps and confirm us

Review Database Diagrams: If you’ve created any diagrams in your SQL Server Management Studio (SSMS), these can showcase relationships visually.

Check sys.foreign_keys: This system view contains information about foreign key relationships. You can run a query like this to retrieve some relationship info:

SELECT 
    fk.name AS ForeignKey,
    tp.name AS ParentTable,
    cp.name AS ParentColumn,
    tr.name AS ReferencedTable,
    cr.name AS ReferencedColumn
FROM 
    sys.foreign_keys AS fk
JOIN 
    sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
JOIN 
    sys.tables AS tp ON fkc.parent_object_id = tp.object_id
JOIN 
    sys.columns AS cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
JOIN 
    sys.tables AS tr ON fkc.referenced_object_id = tr.object_id
JOIN 
    sys.columns AS cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id

Using Extended Properties: You can also store descriptions and additional metadata using extended properties in SQL Server. Here’s how you can query them:

SELECT 
    obj.name AS ObjectName,
    p.name AS PropertyName,
    CAST(value AS NVARCHAR(MAX)) AS PropertyValue
FROM 
    sys.extended_properties p
JOIN 
    sys.objects obj ON p.major_id = obj.object_id
WHERE 
    obj.type IN ('U'); -- 'U' stands for user tables

For capturing additional metadata during the addition of new tables, consider implementing a standard operating procedure where you document relationships and descriptions as part of your deployment workflow.

Answer 2

Hi @phemanth ,

Thanks for the response. I am still checking, currently I see it is manual, and I need to extract individually for all tables.
For example, "Channel" (Similarly we have 400 odd tables.):

Alternatively, the code you provided is still providing the same output as my previous response.
I will get back if I find any solution.

Best Regards,
Hrishi