MySQL 5.0 version of Sysobjects, Syscolumns?

[Barry Beattie]

sorry to be a pest but I've got a MySQL5 database with no documentation and within it I've got a table that I'm trying to find where it's foreign keys are pointing to.

if this was SQLServer I'd be querying syscolumns and sysobjects to find where else the column is mentioned (it's a safe bet in this case that the foreign key/ primary key names will be the same)

so... what's the MySQL version of

SELECT T.name
FROM sysobjects AS T
  INNER JOIN syscolumns AS C
    ON T.ID = C.ID
WHERE T.type = 'U'
   AND C.colname = @column

and

(unlike Enterprise manager) if I can't see the system tables can I still query them?

thanx
b

[Andrew Scott]

Untested

 

SELECT COLUMN_NAME FROM information_schema.`COLUMNS` C WHERE table_name = '<your table name>';

 

Andrew Scott
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613  8676 4223
Mobile: 0404 998 273

[Barry Beattie]

almost (and it put me on the right path), although I'm trying to find the tables with only having the colname ('learning_design_id')

the final answer:

SELECT T.table_name
FROM INFORMATION_SCHEMA.tables T
INNER JOIN INFORMATION_SCHEMA.columns C
ON C.table_name = T.table_name
WHERE C.column_name = 'learning_design_id'


thanx all (esp Haikal)
b