Share via

T-SQL: Fast Code for Relationship within the Database

  • Article

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.  

See Also

  • [[Transact-SQL Portal]]