MySQL 返回表的字段名称的 SQL 命令是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/100504/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the SQL command to return the field names of a table?
提问by WalkingRandomly
Say I have a table called myTable. What is the SQL command to return all of the field names of this table? If the answer is database specific then I need SQL Server right now but would be interested in seeing the solution for other database systems as well.
假设我有一个名为 myTable 的表。返回该表的所有字段名称的 SQL 命令是什么？如果答案是特定于数据库的，那么我现在需要 SQL Server，但也有兴趣查看其他数据库系统的解决方案。
回答by Vinko Vrsalovic
MySQL 3 and 4 (and 5):
MySQL 3 和 4（和 5）：
desc tablename
which is an alias for
这是一个别名
show fields from tablename
SQL Server (from 2000) and MySQL 5:
SQL Server（从 2000 年开始）和 MySQL 5：
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'tablename'
Completing the answer: like people below have said, in SQL Server you can also use the stored procedure sp_help
完成答案：就像下面的人说的，在 SQL Server 中你也可以使用存储过程 sp_help
exec sp_help 'tablename'
回答by hollystyles
SQL-92 standard defines INFORMATION_SCHEMA which conforming rdbms's like MS SQL Server support. The following works for MS SQL Server 2000/2005/2008 and MySql 5 and above
SQL-92 标准定义了符合 rdbms 的 INFORMATION_SCHEMA，如 MS SQL Server 支持。以下适用于 MS SQL Server 2000/2005/2008 和 MySql 5 及更高版本
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'myTable'
MS SQl Server Specific:
MS SQL Server 特定：
exec sp_help 'myTable'
This solution returns several result sets within which is the information you desire, where as the former gives you exactly what you want.
此解决方案返回几个结果集，其中包含您想要的信息，而前者正是您想要的信息。
Also just for completeness you can query the sys tables directly. This is not recommended as the schema can change between versions of SQL Server and INFORMATION_SCHEMA is a layer of abstraction above these tables. But here it is anyway for SQL Server 2000
也只是为了完整性，您可以直接查询 sys 表。不建议这样做，因为架构可以在 SQL Server 版本之间更改，并且 INFORMATION_SCHEMA 是这些表之上的抽象层。但无论如何对于 SQL Server 2000
select [name] from dbo.syscolumns where id = object_id(N'[dbo].[myTable]')
回答by Rene
For those looking for an answer in Oracle:
对于那些在 Oracle 中寻找答案的人：
SELECT column_name FROM user_tab_columns WHERE table_name = 'TABLENAME'
回答by dland
PostgreSQL understands the
PostgreSQL 理解
select column_name from information_schema.columns where table_name = 'myTable'
syntax. If you're working in the psql shell, you can also use
句法。如果你在 psql shell 中工作，你也可以使用
\d myTable
for a description (columns, and their datatypes and constraints)
用于描述（列及其数据类型和约束）
回答by jules
You can use the provided system views to do this:
您可以使用提供的系统视图来执行此操作：
eg
例如
select * from INFORMATION_SCHEMA.COLUMNS
where table_name = '[table name]'
alternatively, you can use the system proc sp_help
或者，您可以使用系统过程sp_help
eg
例如
sp_help '[table name]'
回答by 8jean
Just for completeness, since MySQL and Postgres have already been mentioned: With SQLite, use "pragma table_info()"
只是为了完整性，因为已经提到了 MySQL 和 Postgres：对于 SQLite，使用“ pragma table_info()”
sqlite> pragma table_info('table_name');
cid name type notnull dflt_value pk
---------- ---------- ---------- ---------- ---------- ----------
0 id integer 99 1
1 name 0 0
回答by Graeme Perrow
In Sybase SQL Anywhere, the columns and table information are stored separately, so you need a join:
在 Sybase SQL Anywhere 中，列和表信息是分开存储的，所以需要一个 join：
select c.column_name from systabcol c
key join systab t on t.table_id=c.table_id
where t.table_name='tablename'
回答by Steve Obbayi
This is also MySQL Specific:
这也是 MySQL 特定的：
show fields from [tablename];
this doesnt just show the table names but it also pulls out all the info about the fields.
这不仅显示表名，而且还拉出有关字段的所有信息。
回答by brabster
For IBM DB2 (will double check this on Monday to be sure.)
对于 IBM DB2（将在周一仔细检查以确保。）
SELECT TABNAME,COLNAME from SYSCAT.COLUMNS where TABNAME='MYTABLE'
回答by Veynom
MySQL is the same:
MySQL是一样的：
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tablename'
