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

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

By using this query you get the answer:

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

Answer 6

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

TABLE_NAME is your table