query finding parent child relationship
Using query we can find the Parent-Child reference. This will be helpful to understand the entity relationship model.
select s.name as ChildSchema ,t.name as ChildTable ,o.name as FKConstraint ,col_name( fc.parent_object_id,fc.parent_column_id) as ParentColName ,s2.name as ReferenceSchema ,t2.name as ReferenceTable ,col_name(fc.referenced_object_id,fc.referenced_column_id) as ReferencedColName from sys.foreign_keys f join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id join sys.tables t on f.parent_object_id = t.object_id join sys.schemas s on t.schema_id = s.schema_id join sys.tables t2 on f.referenced_object_id = t2.object_id join sys.schemas s2 on t2.schema_id = s2.schema_id join sys.sysobjects o on object_name(f.object_id) = o.name where o.xtype = 'F' and f.referenced_object_id = object_id('<table name with schema>')
Find the Child Table reference to the Parent table (or) Fact table reference to the Dimension table ? was last modified: February 15th, 2017 by