Can DESCRIBE syntax be embedded in SELECT statement?

mysql sql select information-schema describe
JimZ picture JimZ · Dec 25, 2013 · Viewed 8.7k times · Source

In MySQL, the syntax DESCRIBE can show a table's structure, but it cannot be embedded to normal statement; is there some tricky way to do it?

For example, this shows the table1 structure, returned as a table (but SQL statement does not think so)

DESCRIBE `table1`

But this doesn't work:

SELECT * FROM (DESCRIBE `table1`)

Is there a way to enable it?

I want to join the "table" created by DESCRIBE syntax, how can I do it?

Answer

Saharsh Shah picture Saharsh Shah · Dec 25, 2013

You can use COLUMNS table of INFORMATION_SCHEMA to get expected result as an alternate solution of DESCRIBE table option.

Try this: 

SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `NULL`, 
       COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`
FROM information_schema.COLUMNS  
WHERE TABLE_SCHEMA = 'schemaName' AND TABLE_NAME = 'table1';

Related questions

SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
MySQL SELECT only not null values

Is it possible to do a select statement that takes only NOT NULL values? Right now I am using this: SELECT * FROM table And then I have to filter out the null values with a php loop. Is there a …

Read More
MySQL Select Query - Get only first 10 characters of a value

Ok, so here is the issue. I have a table with some columns and 'subject' is one of the columns. I need to get the first 10 letters from the 'subject' field no matter the 'subject' field contains a string with 100 …

Read More