如何从 SQL Server 中的表中获取列名？

How can I get column names from a table in SQL Server?

提问by odiseh

I would like to query the name of all columns of a table. I found how to do this in:

我想查询一个表的所有列的名称。我发现如何做到这一点：

• Oracle
• MySQL
• PostgreSQL

• 甲骨文
• MySQL
• PostgreSQL

But I need to know: how can this be done in Microsoft SQL Server(2008 in my case)?

但我需要知道：如何在Microsoft SQL Server（在我的情况下为 2008）中做到这一点？

回答by

You can obtain this information and much, much more by querying the Information Schema views.

通过查询信息架构视图，您可以获得这些信息以及更多信息。

This sample query:

此示例查询：

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

可以在所有这些 DB 对象上进行：

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• 列
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• 域
• KEY_COLUMN_USAGE
• 参数
• REFERENTIAL_CONSTRAINTS
• 例行公事
• ROUTINE_COLUMNS
• 图式
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• 表
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• 视图

回答by Arnkrishn

You can use the stored procedure sp_columns which would return information pertaining to all columns for a given table. More info can be found here http://msdn.microsoft.com/en-us/library/ms176077.aspx

您可以使用存储过程 sp_columns，它会返回与给定表的所有列有关的信息。更多信息可以在这里找到http://msdn.microsoft.com/en-us/library/ms176077.aspx

You can also do it by a SQL query. Some thing like this should help:

您也可以通过 SQL 查询来完成。像这样的事情应该有帮助：

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.yourTableName') 

Or a variation would be:

或者一个变体是：

SELECT   o.Name, c.Name
FROM     sys.columns c 
         JOIN sys.objects o ON o.object_id = c.object_id 
WHERE    o.type = 'U' 
ORDER BY o.Name, c.Name

This gets all columns from all tables, ordered by table name and then on column name.

这将获取所有表中的所有列，按表名排序，然后按列名排序。

回答by Limin

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

This is better than getting from sys.columnsbecause it shows DATA_TYPEdirectly.

这比获取更好，sys.columns因为它DATA_TYPE直接显示。

回答by mr_eclair

You can use sp_helpin SQL Server 2008.

您可以sp_help在 SQL Server 2008 中使用。

sp_help <table_name>;

Keyboard shortcut for the above command: select table name (i.e highlight it) and press ALT+F1.

上述命令的键盘快捷键：选择表名（即突出显示它）并按ALT+ F1。

回答by KuldipMCA

By using this query you get the answer:

通过使用此查询，您可以获得答案：

select Column_name 
from Information_schema.columns 
where Table_name like 'table name'

回答by Sachin Parse

You can write this query to get column name and all details without using INFORMATION_SCHEMA in MySql :

您可以编写此查询来获取列名和所有详细信息，而无需在 MySql 中使用 INFORMATION_SCHEMA ：

SHOW COLUMNS FROM database_Name.table_name;

回答by Doc

--This is another variation used to document a large database for conversion (Edited to --remove static columns)

SELECT o.Name                   as Table_Name
     , c.Name                   as Field_Name
     , t.Name                   as Data_Type
     , t.length                 as Length_Size
     , t.prec                   as Precision_
FROM syscolumns c 
     INNER JOIN sysobjects o ON o.id = c.id
     LEFT JOIN  systypes t on t.xtype = c.xtype  
WHERE o.type = 'U' 
ORDER BY o.Name, c.Name

--In the left join, c.type is replaced by c.xtype to get varchar types

回答by bstricks

SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID('TABLE_NAME')

TABLE_NAMEis your table

TABLE_NAME是你的桌子吗

回答by Petko Petkov

SELECT column_name, data_type, character_maximum_length, table_name,ordinal_position, is_nullable 
FROM information_schema.COLUMNS WHERE table_name LIKE 'YOUR_TABLE_NAME'
ORDER BY ordinal_position

回答by Hardeep Singh

Just run this command

只需运行此命令

EXEC sp_columns 'Your Table Name'