SQL statement that returns table and field names in MySQL

July 7, 2008 12:39 PM

Are there SQL statements that return table names and field names for given table names in MySQL Server (5.0.22)?

For example: SELECT name FROM dbo.sysobjects WHERE xtype = 'U' would return table names in SQL Server.

Is there a similar statement in MySQL and one that returns field names also?

posted by joemako to computers & internet (8 comments total)

show tables;
describe [table name];
posted by unixrat at 12:47 PM on July 7 [1 favorite]

show Syntax
posted by JonB at 12:48 PM on July 7

Table name:

select * from information_schema.tables where table_schema = 'whatever your schema is';

Column names:

select * from information_schema.columns where table_schema = ' whatever your schema is' and 'table_name' = 'whatever your table is named';

Easier, for some uses:

show create table `table_name`; -- note: back ticks or nothing, not single quotes here; here it's a table name, not a string.

Referential integrity:

use show create table or select from information_schema.key_column_usage

More here.
posted by orthogonality at 12:51 PM on July 7 [2 favorites]

Note that information_schema.tables contains tabes and views, so the equivalent of your example "SELECT name FROM dbo.sysobjects WHERE xtype = 'U'" is:

SELECT table_name FROM information_schema.tables WHERE table_type= 'BASE_TABLE';

though since this will return tables in all schemas, you'd add the "table_schema = " predicate.
posted by orthogonality at 12:57 PM on July 7

Fuck me. 'BASE TABLE'. No underscore.
posted by orthogonality at 12:59 PM on July 7

@orthogonality, Thanks for your help, that was exactly what I was looking for.

A note on the statement you provided under "Column names:", I had to remove the single quotes around table_name. After that, it worked like a charm.
posted by joemako at 1:31 PM on July 7

joemako writes "I had to remove the single quotes around table_name."

Ah. Whoops. ;)
posted by orthogonality at 2:11 PM on July 7

You might want to prefer using the INFORMATION_SCHEMA views as they're part of the SQL-92 standard and will make it easier for you to switch databases should you ever want to.
posted by zixyer at 6:13 PM on July 7