Visualizing Database Relationships: From SQL Server Schema to Table Relationship Diagrams

Here's how you can visualize these relationships:

-- This code retrieves information about tables and foreign keys

SELECT 
  t.name AS table_name,
  c.name AS column_name,
  kcu.name AS constraint_name,
  REFERENCED_TABLE_NAME AS referenced_table_name
FROM 
  sys.tables t
INNER JOIN 
  sys.columns c ON t.object_id = c.object_id
INNER JOIN 
  sys.foreign_key_constraints k ON t.object_id = k.parent_object_id
INNER JOIN 
  sys.foreign_keys kf ON k.object_id = kf.constraint_object_id
INNER JOIN 
  sys.tables rt ON kf.referenced_object_id = rt.object_id
ORDER BY 
  t.name, c.name;

This code uses system views in SQL Server to get details about tables (.sys.tables.), columns (.sys.columns.), foreign key constraints (.sys.foreign_key_constraints.), and referenced tables (.sys.tables. again).

The output will show you table names, column names, the foreign key constraint name, and the referenced table name. This information can be used to build the logic for your diagram, identifying which tables are connected and how (one-to-one, one-to-many etc.).

• DBVis: This free and open-source tool allows you to connect to your SQL Server database and automatically generate an ER diagram based on the schema. It reads table definitions and foreign key relationships to create a visual representation. [There are paid versions available as well]
• Dataedo: This data documentation platform offers a visual interface for creating ER diagrams. It can connect to your SQL Server database and reverse engineer the schema to generate the diagram. It can even handle cases where foreign key constraints are not explicitly defined.

Scripting with GraphViz:

• SchemaCrawler for SQL Server: This open-source tool can generate database diagrams in various formats using GraphViz, a graph visualization software. It parses the schema information and translates it into a format that GraphViz understands. This method requires some scripting knowledge and familiarity with GraphViz.

Manual Analysis:

• While not ideal for large databases, for smaller schemas, you can manually analyze the table definitions and foreign key relationships to create a basic diagram. This could involve using a drawing tool and manually connecting tables based on the information in the schema.

Choosing the Right Method:

The best method for you depends on the size and complexity of your database, your technical expertise, and desired features.

• For simple databases and quick visualization, third-party GUI tools like DBVis are a good choice.
• For more complex databases or customization needs, consider Dataedo or scripting with GraphViz.
• Manual analysis is only practical for very small databases.