SHOW [FULL] COLUMNS FROM

The statement SHOW [FULL] COLUMNS FROM <table_name> describes the columns of a table or view in a useful tabular format. The optional keyword FULL displays the privileges the current user has to that column, and the comment from the table definition.

The statements SHOW [FULL] FIELDS FROM <table_name>, DESC <table_name>, DESCRIBE <table_name>, and EXPLAIN <table_name> are aliases of this statement.

SHOW COLUMNS FROM - 图1

Note

DESC TABLE <table_name>, DESCRIBE TABLE <table_name>, and EXPLAIN TABLE <table_name> are not equivalent to the above statements. They are aliases of DESC SELECT * FROM .

Synopsis

ShowColumnsFromStmt

SHOW COLUMNS FROM - 图2

TableName

SHOW COLUMNS FROM - 图3

ShowLikeOrWhere

SHOW COLUMNS FROM - 图4

  1. ShowColumnsFromStmt ::=
  2.     "SHOW" "FULL"? ("COLUMNS" | "FIELDS") ("FROM" | "IN") TableName ( ("FROM" | "IN") SchemaName)? ShowLikeOrWhere?
  4. TableName ::=
  5.     (Identifier ".")? Identifier
  7. ShowLikeOrWhere ::=
  8.     "LIKE" SimpleExpr
  9. |   "WHERE" Expression

Examples

  1. mysql> CREATE VIEW v1 AS SELECT 1;
  2. Query OK, 0 rows affected (0.11 sec)
  4. mysql> SHOW COLUMNS FROM v1;
  5. +-------+-----------+------+------+---------+-------+
  6. | Field | Type      | Null | Key  | Default | Extra |
  7. +-------+-----------+------+------+---------+-------+
  8. | 1     | bigint(1) | YES  |      | NULL    |       |
  9. +-------+-----------+------+------+---------+-------+
  10. 1 row in set (0.00 sec)
  12. mysql> DESC v1;
  13. +-------+-----------+------+------+---------+-------+
  14. | Field | Type      | Null | Key  | Default | Extra |
  15. +-------+-----------+------+------+---------+-------+
  16. | 1     | bigint(1) | YES  |      | NULL    |       |
  17. +-------+-----------+------+------+---------+-------+
  18. 1 row in set (0.00 sec)
  20. mysql> DESCRIBE v1;
  21. +-------+-----------+------+------+---------+-------+
  22. | Field | Type      | Null | Key  | Default | Extra |
  23. +-------+-----------+------+------+---------+-------+
  24. | 1     | bigint(1) | YES  |      | NULL    |       |
  25. +-------+-----------+------+------+---------+-------+
  26. 1 row in set (0.00 sec)
  28. mysql> EXPLAIN v1;
  29. +-------+-----------+------+------+---------+-------+
  30. | Field | Type      | Null | Key  | Default | Extra |
  31. +-------+-----------+------+------+---------+-------+
  32. | 1     | bigint(1) | YES  |      | NULL    |       |
  33. +-------+-----------+------+------+---------+-------+
  34. 1 row in set (0.00 sec)
  36. mysql> SHOW FIELDS FROM v1;
  37. +-------+-----------+------+------+---------+-------+
  38. | Field | Type      | Null | Key  | Default | Extra |
  39. +-------+-----------+------+------+---------+-------+
  40. | 1     | bigint(1) | YES  |      | NULL    |       |
  41. +-------+-----------+------+------+---------+-------+
  42. 1 row in set (0.00 sec)
  44. mysql> SHOW FULL COLUMNS FROM v1;
  45. +-------+-----------+-----------+------+------+---------+-------+---------------------------------+---------+
  46. | Field | Type      | Collation | Null | Key  | Default | Extra | Privileges                      | Comment |
  47. +-------+-----------+-----------+------+------+---------+-------+---------------------------------+---------+
  48. | 1     | bigint(1) | NULL      | YES  |      | NULL    |       | select,insert,update,references |         |
  49. +-------+-----------+-----------+------+------+---------+-------+---------------------------------+---------+
  50. 1 row in set (0.00 sec)
  52. mysql> SHOW FULL COLUMNS FROM mysql.user;
  53. +------------------------+---------------+-------------+------+------+---------+-------+---------------------------------+---------+
  54. | Field                  | Type          | Collation   | Null | Key  | Default | Extra | Privileges                      | Comment |
  55. +------------------------+---------------+-------------+------+------+---------+-------+---------------------------------+---------+
  56. | Host                   | char(255)     | utf8mb4_bin | NO   | PRI  | NULL    |       | select,insert,update,references |         |
  57. | User                   | char(32)      | utf8mb4_bin | NO   | PRI  | NULL    |       | select,insert,update,references |         |
  58. | authentication_string  | text          | utf8mb4_bin | YES  |      | NULL    |       | select,insert,update,references |         |
  59. | plugin                 | char(64)      | utf8mb4_bin | YES  |      | NULL    |       | select,insert,update,references |         |
  60. | Select_priv            | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  61. | Insert_priv            | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  62. | Update_priv            | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  63. | Delete_priv            | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  64. | Create_priv            | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  65. | Drop_priv              | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  66. | Process_priv           | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  67. | Grant_priv             | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  68. | References_priv        | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  69. | Alter_priv             | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  70. | Show_db_priv           | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  71. | Super_priv             | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  72. | Create_tmp_table_priv  | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  73. | Lock_tables_priv       | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  74. | Execute_priv           | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  75. | Create_view_priv       | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  76. | Show_view_priv         | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  77. | Create_routine_priv    | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  78. | Alter_routine_priv     | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  79. | Index_priv             | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  80. | Create_user_priv       | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  81. | Event_priv             | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  82. | Repl_slave_priv        | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  83. | Repl_client_priv       | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  84. | Trigger_priv           | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  85. | Create_role_priv       | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  86. | Drop_role_priv         | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  87. | Account_locked         | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  88. | Shutdown_priv          | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  89. | Reload_priv            | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  90. | FILE_priv              | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  91. | Config_priv            | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  92. | Create_Tablespace_Priv | enum('N','Y') | utf8mb4_bin | NO   |      | N       |       | select,insert,update,references |         |
  93. | User_attributes        | json          | NULL        | YES  |      | NULL    |       | select,insert,update,references |         |
  94. +------------------------+---------------+-------------+------+------+---------+-------+---------------------------------+---------+
  95. 38 rows in set (0.00 sec)

MySQL compatibility

The SHOW [FULL] COLUMNS FROM statement in TiDB is fully compatible with MySQL. If you find any compatibility differences, report a bug.

See also