SQL statement that returns table and field names in MySQL
July 7, 2008 12:39 PM Subscribe
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?
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?
Best answer: 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, 2008 [2 favorites]
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, 2008 [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, 2008
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, 2008
Fuck me. 'BASE TABLE'. No underscore.
posted by orthogonality at 12:59 PM on July 7, 2008
posted by orthogonality at 12:59 PM on July 7, 2008
Response by poster: @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, 2008
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, 2008
joemako writes "I had to remove the single quotes around table_name."
Ah. Whoops. ;)
posted by orthogonality at 2:11 PM on July 7, 2008
Ah. Whoops. ;)
posted by orthogonality at 2:11 PM on July 7, 2008
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, 2008
posted by zixyer at 6:13 PM on July 7, 2008
This thread is closed to new comments.
describe [table name];
posted by unixrat at 12:47 PM on July 7, 2008 [1 favorite]