The following will give you a jump start. This will tell you all the
- FK's for a database ( 'yourdatabase')
- the referenced table
- referenced collumn name
- and the constraint name
For Mysql 5.x
SELECT `COLUMNS`.TABLE_SCHEMA, `KEY_COLUMN_USAGE`.REFERENCED_TABLE_NAME,
`KEY_COLUMN_USAGE`.REFERENCED_COLUMN_NAME, `KEY_COLUMN_USAGE`.
POSITION_IN_UNIQUE_CONSTRAINT, `KEY_COLUMN_USAGE`.ORDINAL_POSITION
AS
`ORDINAL_POSITION_1`, `COLUMNS`.TABLE_NAME, `COLUMNS`.COLUMN_KEY,
`COLUMNS`.COLUMN_NAME
FROM information_schema.COLUMNS `COLUMNS`
LEFT OUTER JOIN
information_schema.KEY_COLUMN_USAGE `KEY_COLUMN_USAGE`
ON (`COLUMNS`.TABLE_SCHEMA = `KEY_COLUMN_USAGE`.TABLE_SCHEMA)
WHERE (`COLUMNS`.TABLE_SCHEMA = 'yourdatabase')
AND (`KEY_COLUMN_USAGE`.REFERENCED_TABLE_SCHEMA = 'yourdatabase')
AND (`COLUMNS`.COLUMN_KEY = 'MUL')
On Tue, Mar 18, 2008 at 4:00 PM, Sonny Savage <[EMAIL PROTECTED]> wrote:
> All RDBMS platforms I've used provide a data dictionary. These are
> special
> tables that you can query against to get structural information. MySQL
> has
> a data dictionary and it's documented here:
> http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
>
> You may want to create a CFC that queries these tables and provides you an
> easy-to-use data dictionary (a quick Googling didn't produce an already
> available item).
>
> On Tue, Mar 18, 2008 at 4:03 PM, Richard White <[EMAIL PROTECTED]> wrote:
>
> > thanks for this - is there any way to get the constraint name for a
> > particular column, for example the constraint name in the above example
> is
> > qu_41_tb_24_ibfk2. but i wont know the name unless there is a way to get
> it
> > from the database. if i get this then i can delete this first then
> remove
> > the foreign key and it will remove fine
> >
> > thanks very much
> >
> >
> >
> > ><cfdbinfo type="columns" table="yourtable" datasource="yourDSN"
> > >name="dbdata">
> > >
> > ><cfquery name="getfk" dbtype="query">
> > > SELECT REFERENCED_PRIMARYKEY, REFERENCED_PRIMARYKEY_TABLE
> > > FROM dbdata
> > > WHERE IS_FOREIGNKEY = 'YES'
> > ></cfquery>
> > >
> > ><cfdump var="dbdata">
> > >
> > >
> > ><cfdump var="getfk">
> > >
> > >
> > >
> > >On Tue, Mar 18, 2008 at 11:11 AM, Richard White <[EMAIL PROTECTED]>
> > wrote:
> > >
> > >>
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:301542
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
