To know about the database it is good for one if he/she looking for all references for a object
- whats tables are dependent on MAIN table (Child Objects)
- On which tables MAIN table is dependent (Parent Objects)
Suppose i have 3 tables and in parenthesis show its fields
Dept (dept_id, Dept_name )
Emp (emp_id, Emp_first_name, emp_last_name , dept_id)
Sal(sal_id, year, emp_id)
Here EMP is Main table and Dept is Parent table (for Main table) and Sal is Child table (for Main table).
So one can easily understand the database if he has knowledge of both kind of references
Below is the script to identify the same.
SELECT
f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM
sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
where
OBJECT_NAME(f.parent_object_id)='MAIN_TABLE'
or OBJECT_NAME (f.referenced_object_id) = 'MAIN_TABLE'
