;
with
cte
as
(
select
constraint_object_id, constraint_column_id, c.parent_object_id
as
parentobjectid, parent_column_id
, referenced_object_id, referenced_column_id,
name
as
parentname
from
sys.foreign_key_columns c
inner
join
sys.tables
on
c.parent_object_id=object_id)
,cte2
as
(
select
constraint_object_id, constraint_column_id, parentobjectid, referenced_object_id, parent_column_id, parentname, referenced_column_id,
name
as
referencedname
from
cte ct
inner
join
sys.tables
on
ct.referenced_object_id=object_id)
, cte3
as
(
select
constraint_object_id, constraint_column_id, parentobjectid, parent_column_id
, referenced_object_id, referenced_column_id, parentname, referencedname,
name
as
parentcolumname
from
cte2
inner
join
sys.all_columns cl
on
parentobjectid=cl.object_id
where
cl.column_id=parent_column_id)
select
constraint_object_id, constraint_column_id, parentobjectid, parent_column_id
, referenced_object_id, referenced_column_id, parentname
as
ParentTable, referencedname
as
ReferencedTable, parentcolumname
as
parentsColumn,
name
as
ReferencedColumn
from
cte3
inner
join
sys.all_columns cl
on
referenced_object_id=cl.object_id
where
cl.column_id=referenced_column_id
order
by
ParentTable
Another purpose of this code is that (after having saved the results in a table for example) it can be compared. That means that if you save the last result in a table that you can call LastRelantionship dated February 2013 and you being called after months for another contract in the same company because "maybe someone changed something and now the software doesn't work or the statistics are wrong", you can run the same query, building a new table LastRelantionship date October 2013 and after comparing the two tables you can quickly find out if someone touched the relationships (believe me, this can happen pretty frequently).
So, I hope this code can help everyone to be faster in case of job contract issues.
order
by
ParentTable
Another purpose of this code is that (after having saved the results in a table for example) it can be compared. That means that if you save the last result in a table that you can call LastRelantionship dated February 2013 and you being called after months for another contract in the same company because "maybe someone changed something and now the software doesn't work or the statistiee_Also">See Also