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

 

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

  • Oracle
  • MySQL
  • PostgreSQL

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

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

    This sample query: 

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

    Can be made over all these DB objects:

  • 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

    • 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

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

    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. 

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

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

    链接地址: http://www.djcxy.com/p/2734.html

    上一篇: 在关系数据库中存储分层数据有哪些选项？

    下一篇: 我如何从SQL Server中的表中获取列名？