Query to count table dependencies

Asked

Viewed 207 times

1

Let’s assume I have a database with only 50 tables.

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

There is a way, so to make a query that brings the amount of dependencies of all tables in the database ?

For example:

  • Table ZZZ has FK of table XXX and YYY
  • Table XXX has FK from WWW table
  • The WWW table has no FK

Expected result:

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

  • yes, which database?

  • Dude... anyone... if you can even put in the most common ones: sql-server, mysql, etc... nice that keeps a question already for several... gets a very useful record...

  • 1

    via tool in sql server you can in the management studio see the dependencies, and mysql the TOAD has the same function, now as query, I will put as an answer

1 answer

1


Only SQL-SERVER you can link the table foreign_keys with the table sysobjects for that reason:

 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

In the MySQL can be like this, using table 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: http://sqlfiddle.com/#! 9/36ccc0/1

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.