EXCELLENT! Thanks! > -----Original Message----- > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Friday, June 30, 2006 8:31 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > 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 > >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
