Know relationships between all the tables of database in SQL Server

sql

database-diagrams

information-schema

recursive-relationships

byNikita Barsukov·Dec 26, 2024

Discover SQL Server table relationships with a direct query to system views. Extract all foreign key constraints using the following query:

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
    /* Ah, joining tables, the Olympic games of SQL */     
INNER JOIN 
    sys.tables AS tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables AS tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
    /* Column matching: the eye test of SQL */
INNER JOIN 
    sys.columns AS cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns AS cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id

This outputs a relationship map featuring each foreign key (ForeignKey), the corresponding source table (ParentTable), source column (ParentColumn), referenced table (ReferencedTable), and referenced column (ReferencedColumn). Ideal for comprehensive database schema analysis.

Export to Excel for advanced analysis

After extracting relationship maps with SQL, import the results to Excel for comprehensive analysis. Here's how:

Execute the SQL query in SSMS.
Right-click on the results, select "Copy with Headers".
Paste into Excel and leverage features like filtering and pivot tables for further exploration.

This way, you can hop between tables like a rabbit in a veggie garden (see what I did there? 'rabbit'-'tables'-'veggie garden' 😄).

Leveraging textual documentation

While diagrams can be great for understanding table relationships, textual documentation is like your health insurance - you don't know you need it until you do. It provides more comprehensive information and can come in handy in complex database settings or in the absence of visual tools.

Using the right tools for the job

Ever tried gardening with a spoon? Sure you can do it, but doesn't a shovel sound better? Likewise, using the right tools and techniques can make navigating the database "wilderness" more pleasant:

Microsoft Visio is like your GPS: it reverse engineers database diagrams from existing databases and makes navigation smoother with its user-friendly interface.
Schemacrawler brings the power of AI to your rescue, providing extra "vision" for understanding essential PK/FK/UK relationships.
Custom stored procedures are like your travel guides and can be used to visualize relationships in a hierarchical "tree".

Combining documentation and visualization

When it comes to a comprehensive understanding of your database schema, textual documentation and visual representation go together like fish and chips. They complement each other, providing a 360-degree view of the complex world of database structures.

Digging deeper into database relationships

Using INFO SCHEMA for advanced querying

Extend your understanding by querying the INFORMATION_SCHEMA. It's less of a sys view and more of a crystal ball, providing a way to unearth hidden relationships:

SELECT 
    CONSTRAINT_NAME, 
    TABLE_NAME, 
    COLUMN_NAME, 
    REFERENCED_TABLE_NAME, 
    REFERENCED_COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
    TABLE_SCHEMA = 'YourSchemaName';

Unraveling recursive relationships

Like tangled headphones, self-referencing or recursive relationships can be tricky to untangle. Use recursive common table expressions (CTEs) as your "detangling spray" for deeper insight.

Tailoring visual and textual representations

Like a bespoke suit, your data representation can be customized to fit your specific needs and preferences. Some enjoy the sight of visualised relationships, while others might prefer the clear-cut orderliness of textual lists. Finding your sweet spot is key.

explain-codes / Sql / Know relationships between all the tables of database in SQL Server

Linked

What is a good reason to use SQL views?



How do I generate a SQL script from my diagram in MySQL Workbench?



What is the equivalent of 'describe table' in SQL Server?



Adding a column description



Export table data from one SQL Server to another

