CODEWITHSUNDEEP
mysql
GeoGo
GeoGo

Reputation: 2388

Dependency objects MySQL

Advise the GUI tool for viewing dependent object database MySQL.
for example:

USE db;

CREATE TABLE t1(
  id INT(11) NOT NULL,
  `column` INT(11) DEFAULT NULL,
  UNIQUE INDEX id (id)
);

CREATE TABLE t2(
  id INT(11) NOT NULL,
  `column` INT(11) DEFAULT NULL,
  INDEX FK_t2_t1_id (`column`),
  CONSTRAINT FK_t2_t1_id FOREIGN KEY (`column`)
  REFERENCES t1 (id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

CREATE TABLE t3(
  id INT(11) NOT NULL,
  `column` INT(11) DEFAULT NULL,
  INDEX FK_t3_t1_id (`column`),
  CONSTRAINT FK_t3_t1_id FOREIGN KEY (`column`)
  REFERENCES t1 (id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

DELIMITER $$

CREATE DEFINER = 'root'@'localhost'
PROCEDURE procedure1()
BEGIN
  SELECT
    *
  FROM
    db.t1;
END
$$
DELIMITER ;

I want to see which objects refer to the table t1. In this example it are t2, t3 and procedure1

Upvotes: 0

Views: 1424

Answers (2)

Benjamin Stein
Benjamin Stein

Reputation: 31

This is most likely what you are looking for. The information can be found in information_schema.

     SELECT pk.constraint_schema       AS PKDatabaseName
          , pk.table_name              AS PKObjectName
          , fk_cols.column_name        AS PKColumnName
          , fk_cols.referenced_table_schema AS FKDatabaseName
          , fk_cols.referenced_table_name   AS FKObjectName
          , fk_cols.referenced_column_name  AS FKColumnName
          , pk.constraint_name         AS ConstraintName
          , fk_cols.ordinal_position   AS ColumnIdx
       FROM information_schema.table_constraints pk
 INNER JOIN information_schema.key_column_usage  fk_cols
         ON pk.constraint_schema = fk_cols.table_schema
        AND pk.table_name        = fk_cols.table_name
        AND pk.constraint_name   = fk_cols.constraint_name
      WHERE pk.constraint_type = 'FOREIGN KEY'

Upvotes: 3

Manzabar
Manzabar

Reputation: 676

Your question isn't all that clear, but it sounds like you're looking for some sort of GUI tool to do data modeling with MySQL. If that's correct, try looking at MySQL Workbench.

Upvotes: 1

Related Questions