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

 



Supply hyperlink

Leave a Reply

Your email address will not be published. Required fields are marked *