Count tables’ references and tables’ foreign keys

There are some cases that you do not have any documentations for the database that you are working on and you need to investigate the entity relationships and so on. We can understand the database entity relationships better if we can find the number of references to the table or the number of tables’ dependencies. We can do the job easily by combining sys.tables, sys.foreign_keys and/or sys.sysreferences. The following simple T-SQL codes will retrieve tables’ references and tables’ dependencies:

• Count table dependencies:

select t.name [Table Name]

     ,count(f.name) [Count Dependencies]

from sys.foreign_keys f inner join sys.tables t on f.parent_object_id=t.object_id

group by t.name

order by 2 desc

Here is an alternative way with the same results:

select t.name [Table name]

 , count(r.rkeyid) [Number of References]

from sys.tables t inner join sys.sysreferences r on r.rkeyid=t.object_id

group by t.name

order by 2 desc

• Count table references:

select t.name [Table Name]

     ,count(f.referenced_object_id) [Number of References]

from sys.foreign_keys f inner join sys.tables t on f.referenced_object_id=t.object_id

group by t.name

order by 2 desc

Here is an alternative way with the same results:

select t.name [Table name]

 , count(r.fkeyid) [Number of References]

from sys.tables t inner join sys.sysreferences r on r.fkeyid=t.object_id

group by t.name

order by 2 desc