T-SQL Desk References and International Keys
There are some circumstances that you simply shouldn’t have any documentations for the database that you’re engaged on and it is advisable to examine the entity relationships and so forth. We will perceive the database entity relationships higher if we will discover the variety of references to the desk or the variety of tables’ dependencies. We will do the job simply by combining sys.tables, sys.foreign_keys and/or sys.sysreferences. The next easy T-SQL codes will retrieve tables’ references and tables’ dependencies:
choose t.title [Table Name]
,depend(f.title) [Count Dependencies]
from sys.foreign_keys f internal be part of sys.tables t on f.parent_object_id=t.object_id
group by t.title
order by 2 desc
Right here is an alternate manner with the identical outcomes:
choose t.title [Table name]
, depend(r.rkeyid) [Number of References]
from sys.tables t internal be part of sys.sysreferences r on r.rkeyid=t.object_id
group by t.title
order by 2 desc
choose t.title [Table Name]
,depend(f.referenced_object_id) [Number of References]
from sys.foreign_keys f internal be part of sys.tables t on f.referenced_object_id=t.object_id
group by t.title
order by 2 desc
Right here is an alternate manner with the identical outcomes:
choose t.title [Table name]
, depend(r.fkeyid) [Number of References]
from sys.tables t internal be part of sys.sysreferences r on r.fkeyid=t.object_id
group by t.title
order by 2 desc
