Sometimes one needs to find out all the relationships within a database. For example, if you are a contractor and you go to new company even for only one day, just to make some new report requested by the boss or other  similar stuff;  probably you need fast code that you can keep in your personal code folder, just for a quick copy and paste:
;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