List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:July 1 2006 3:53am
Subject:RE: Need way to see all relations?
View as plain text  
EXCELLENT! Thanks! 

> -----Original Message-----
> From: Peter Brawley [mailto:peter.brawley@stripped] 
> Sent: Friday, June 30, 2006 8:31 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: Need way to see all relations?
> 
> 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
> 
> 

Thread
Need way to see all relations?Daevid Vincent1 Jul
Re: Need way to see all relations?Peter Van Dijck1 Jul
  • RE: Need way to see all relations?Daevid Vincent1 Jul
Re: Need way to see all relations?Peter Van Dijck1 Jul
Re: Need way to see all relations?Peter Brawley1 Jul
  • RE: Need way to see all relations?Daevid Vincent1 Jul