How do you return the column names of a table?

Question

How would I return the column names of a table using SQL Server 2008? i.e. a table contains these columns- id, name, address, country and I want to return these as data.

Answer 1

This is the easiest way

exec sp_columns [tablename]

Answer 2

I use

SELECT st.NAME, sc.NAME, sc.system_type_id
FROM sys.tables st
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
WHERE st.name LIKE '%Tablename%'

Answer 3

DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE  Table_name = 'MxLocations';
SELECT @col;

Answer 4

This seems a bit easier then the above suggestions because it uses the OBJECT_ID() function to locate the table's id. Any column with that id is part of the table.

SELECT * 
  FROM syscolumns 
 WHERE id=OBJECT_ID('YOUR_TABLE') 

I commonly use a similar query to see if a column I know is part of a newer version is present. It is the same query with the addition of {AND name='YOUR_COLUMN'} to the where clause.

IF EXISTS (
        SELECT * 
          FROM syscolumns 
         WHERE id=OBJECT_ID('YOUR_TABLE') 
           AND name='YOUR_COLUMN'
        )
BEGIN
    PRINT 'Column found'
END

Answer 5

I just use a query like Martin Smith mentioned, just little shorter:

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

Answer 6

set fmtonly on
select * from yourTable