How can I get column names from a table in SQL Ser

问题:

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)?

回答1:

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:

• 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

回答2:

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.

回答3:

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

This is better than getting from sys.columns because it shows DATA_TYPE directly.

回答4:

You can use sp_help in SQL Server 2008.

sp_help <table_name>;

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

回答5:

By using this query you get the answer:

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

回答6:

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

SHOW COLUMNS FROM database_Name.table_name;

回答7:

--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

回答8:

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

TABLE_NAME is your table

回答9:

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

回答10:

This SO question is missing the following approach :

-- List down all columns of table 'Logging'
select * from sys.all_columns where object_id = OBJECT_ID('Logging')

回答11:

Just run this command

EXEC sp_columns 'Your Table Name'

回答12:

You can try this.This gives all the column names with their respective data types.

desc <TABLE NAME> ;

回答13:

It will check whether the given the table is Base Table.

SELECT 
    T.TABLE_NAME AS 'TABLE NAME',
    C.COLUMN_NAME AS 'COLUMN NAME'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME
    WHERE   T.TABLE_TYPE='BASE TABLE'
            AND T.TABLE_NAME LIKE 'Your Table Name'

回答14:

you can use this query

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'

回答15:

SELECT c.Name 
FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.object_id = OBJECT_ID('TABLE_NAME')
ORDER BY c.Name

回答16:

One other option which is arguably more intuitive is:

SELECT [name] 
FROM sys.columns 
WHERE object_id = OBJECT_ID('[yourSchemaType].[yourTableName]') 

This gives you all your column names in a single column. If you care about other metadata, you can change edit the SELECT STATEMENT TO SELECT *.

回答17:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'name_of_your_table'

回答18:

SELECT TOP (0) [toID]
      ,[sourceID]
      ,[name]
      ,[address]
  FROM [ReportDatabase].[Ticket].[To]

Simple and doesnt require any sys tables

回答19:

Summarizing the Answers

I can see many different answers and ways to do this but there is the rub in this and that is the objective.

Yes, the objective. If you want to only know the column names you can use

SELECT * FROM my_table WHERE 1=0
or
SELECT TOP 0 * FROM my_table

But if you want to use those columns somewhere or simply say manipulate them then the quick queries above are not going to be of any use. You need to use

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

one more way to know some specific columns where we are in need of some similar columns

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'