Query to count table dependencies

Question

Query to count table dependencies

Navigation

#1 by (1 votes)

1

Let's say I have a database with only 50 tables.

In the structure, I have several links, several foreign keys.

Is there a way, how to make a query that brings up the amount of dependencies of all the bank tables ?

For example:

Table ZZZ has FK of table XXX and YYY
Table XXX has FK from the WWW table
The WWW table does not have FK

Expected result:

tabela | dependencias
 ZZZ   |      2
 XXX   |      1
 WWW   |      0

sql

asked by anonymous 06.06.2018 / 18:01

1 answer

What causes the error "(.text + 0x5b5) undefined reference to 'printf'"? Copy Cells When Identifying Change Worksheet_Change (Excel VB)

score 1 · Accepted Answer

So SQL-SERVER you can link the foreign_keys table to the sysobjects table for this:

 SELECT
   s.name,
   count(f.name) Dependencias
FROM sys.foreign_keys AS f
INNER JOIN sys.sysobjects s
   ON f.parent_object_id = s.id
group by s.name
ORDER BY s.name

No MySQL can be like this, using table_constraints:

select distinct ref.referenced_table_name Tabela,
       count(ref.referenced_table_name) Dependencias
  from information_schema.table_constraints tab
 inner join information_schema.referential_constraints ref
    on tab.constraint_name = ref.constraint_name
group by ref.referenced_table_name
order by ref.referenced_table_name;

I created a fiddle to exemplify: link