Is there a query that DESCRIBE runs behind the scenes?

For example:

• DESCRIBE myTable
• DESCRIBE myView

Is there something I can run against the informational_schema to run that query, or it's internal-only?

CodePudding user response：

I don't think DESCRIBE is standard SQL. It is provided in MySQL for compatibility with Oracle statement of the same name. There's a comment to that effect here: https://github.com/mysql/mysql-server/blob/8.0/sql/sql_yacc.yy#L13974-L13980

/* A Oracle compatible synonym for show */
describe_stmt:
          describe_command table_ident opt_describe_column

Both SHOW COLUMNS and DESCRIBE <table> end up calling the same function, build_show_columns_query(), implemented here:

https://github.com/mysql/mysql-server/blob/8.0/sql/dd/info_schema/show.cc#L612-L617

It's basically a query against INFORMATION_SCHEMA.COLUMNS, with a WHERE clause to select for one specific schema and table, and optionally other user-defined conditions. You could write the same query yourself.

Other implementations of SQL have different commands to give similar metadata:

• PostgreSQL uses the \d <table> client command: https://www.educba.com/postgresql-describe-table/
• Microsoft SQL Server uses exec sp_columns <table>: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-columns-transact-sql?view=sql-server-ver15
• SQLite uses pragma table_info(<table>): https://www.sqlitetutorial.net/sqlite-describe-table/
• Informix uses info columns for <table>: https://www.ibm.com/docs/en/informix-servers/14.10?topic=statements-info-statement

If you want to know the equivalent for any other brands, probably you can do a google search for "describe table in <brand>".