BI SQL # 116 : SQL Server DBA Scripts : Find All Database table relation in 1-* format
Hi Geeks,
In this article we are going to cover How to Find All Database table relation in 1-* format.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- SQL Script Output Column
- SQL Script Code
- SQL Script Output Screenshot
- User Level to execute
Problem Statement of SQL Script:
How can find All database table relationship in 1-* format?
Description of SQL Script:
All database table relationship in 1-* format is for Documentation and high level table relationship findings.
SQL Script Output Column
TablesWithRelations
SQL Script Code
SELECT CASE WHEN a.parent_object_id IS NULL THEN parent.NAME + '-1--*-' + child.NAME ELSE parent.NAME + '-1--1-' + child.NAME END AS TablesWithRelations FROM ( SELECT DISTINCT parent_object_id ,referenced_object_id FROM sys.foreign_keys ) fk LEFT JOIN ( SELECT DISTINCT fkindexes.parent_object_id ,fkindexes.referenced_object_id FROM ( SELECT fk.parent_object_id ,fk.referenced_object_id ,ixcolumns.index_id ,COUNT(*) cindexes FROM ( SELECT object_id ,parent_object_id ,referenced_object_id FROM ( SELECT row_number() OVER ( PARTITION BY parent_object_id ,referenced_object_id ORDER BY object_id ) rid ,object_id ,parent_object_id ,referenced_object_id FROM sys.foreign_keys ) fk WHERE rid = 1 ) fk JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk. object_id JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fkc. parent_object_id AND ixcolumns.column_id = fkc.parent_column_id JOIN sys.indexes ix ON ix.object_id = ixcolumns.object_id AND ix.index_id = ixcolumns.index_id WHERE ix.is_unique = 1 GROUP BY fk.parent_object_id ,fk.referenced_object_id ,ixcolumns.index_id ) fkindexes JOIN ( SELECT fk.parent_object_id ,ixcolumns.index_id ,COUNT(*) cindexestotal FROM ( SELECT DISTINCT parent_object_id FROM sys.foreign_keys ) fk JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fk. parent_object_id GROUP BY fk.parent_object_id ,ixcolumns.index_id ) totalindexes ON totalindexes.parent_object_id = fkindexes. parent_object_id AND totalindexes.index_id = fkindexes.index_id WHERE cindexestotal - cindexes = 0 ) a ON a.parent_object_id = fk.parent_object_id AND a.referenced_object_id = fk.referenced_object_id JOIN sys.tables child ON fk.parent_object_id = child.object_id JOIN sys.tables parent ON fk.referenced_object_id = parent.object_id ORDER BY TablesWithRelations
SQL Script Output Screenshot
User Level to execute
- 100
Hope you will like How to Find All Database table relation in 1-* format.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
SQL Server Mentalist 