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

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

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.

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.

By using this query you get the answer:

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

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

You can try using :-

USE db_name;
DESCRIBE table_name;

it'll give you column names with the type.

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

In SQL Server, you can select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS.

Here is the code:

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

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

Simple and doesnt require any sys tables

Simple and doesn't require sys variables:

SHOW COLUMNS FROM suppliers;

Some SQL Generating SQL:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
  col001 INTEGER
, col002 INTEGER
, col003 INTEGER
, col004 INTEGER
, col005 INTEGER
, col006 INTEGER
, col007 INTEGER
, col008 INTEGER
, col009 INTEGER
, col010 INTEGER
)
;
INSERT INTO test(col001) VALUES(1);
INSERT INTO test(col002) VALUES(1);
INSERT INTO test(col005) VALUES(1);
INSERT INTO test(col009) VALUES(1);
INSERT INTO test VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

SELECT
  CASE ROW_NUMBER() OVER(ORDER BY ordinal_position)
  WHEN 1 THEN 
    'SELECT'+CHAR(10)+'  *'+CHAR(10)+'FROM test'
   +CHAR(10)+'WHERE '
  ELSE
    '   OR '
  END
+ column_name +' IS NOT NULL'
+ CASE ROW_NUMBER() OVER(ORDER BY ordinal_position DESC)
  WHEN 1 THEN 
    CHAR(10)+';'
  ELSE
    ''
  END
  FROM information_schema.columns
  WHERE table_schema='dbo'
    AND table_name = 'test'
ORDER BY
  ordinal_position;

-- the whole scenario. Works for 10 , will work for 100, too:

-- out -----------------------------------------------
-- out  SELECT
-- out   *
-- out FROM test
-- out WHERE col001 IS NOT NULL
-- out     OR col002 IS NOT NULL
-- out     OR col003 IS NOT NULL
-- out     OR col004 IS NOT NULL
-- out     OR col005 IS NOT NULL
-- out     OR col006 IS NOT NULL
-- out     OR col007 IS NOT NULL
-- out     OR col008 IS NOT NULL
-- out     OR col009 IS NOT NULL
-- out     OR col010 IS NOT NULL
-- out ;

try using : "desc Table_name" only thing is it will give other details as well like ,Is Null ,Type and constraints

This works for me

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'tableName';