From: Peter Brawley
Date: July 1 2006 3:30am
Subject: Re: Need way to see all relations?
List-Archive: http://lists.mysql.com/mysql/199552
Message-Id: <44A5EC61.5040500@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Daevid,

>I have a 'users' table. And there are all kinds of related
>tables like 'logs', 'preferences', etc. that all have a FK 
>reference back to this user ID.
>I want to make a query or use some PHP to build a list of 
>all tables in my DB that are references.

Query to find tables with FK references to $db.$table:

SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name
FROM information_schema.table_constraints AS c
INNER JOIN information_schema.key_column_usage AS u
USING( constraint_schema, constraint_name )
WHERE c.constraint_type = 'FOREIGN KEY' .
  AND u.referenced_table_schema='$db'
  AND u.referenced_table_name = '$table'
  ORDER BY c.table_schema,u.table_name;

PB

Daevid Vincent wrote:
> Is there a way with InnoDB tables to see all related tables/columns?
>
> Basically what I want to do is make a script that somehow will dynamically
> create a "backup" of a single user record. But I don't want to have to
> manually add a new table or column everytime to the script.
>
> So for example, I have a 'users' table. And there are all kinds of related
> tables like 'logs', 'preferences', etc. that all have a FK reference back to
> this user ID.
>
> I want to make a query or use some PHP to build a list of all tables in my
> DB that are references.
>
> Then I can just dump out that user ID (via this script) and it will backup
> that user and all related table data across all tables.
>
> ÐÆ5ÏÐ 
>
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006