Is there a succinct way to retrieve a list of table column names with T-SQL?

Question

When I'm looking round a SQL Server database I often want to interactively retrieve a simple list of column names for a database table (no data type information etc., just the names) using sqlcmd.

I can do this:

EXEC sp_columns @table_name = 'tablename'

which gives me much more than I want and wraps in a command prompt to the extent that it is close to unintelligible, or I can do this:

SELECT col.name 
FROM sysobjects obj 
INNER JOIN syscolumns col
ON obj.id = col.id where obj.name = 'tablename'

which gives me what I want but is a bit verbose.

I'd like to be able to do something like this:

SELECT column_name
FROM (EXEC sp_columns @table_name = 'tablename')

but that doesn't work (see, for example, this question).

Is there a more succinct way to do this (i.e. one that is easier to type at the command line and therefore less error prone)?

Answer 1

Look at the ANSI-defined INFORMATION_SCHEMA views. Try SELECT * FROM INFORMATION_SCHEMA.COLUMNS for starters and go on from there.

Your example would require this SQL:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'tablename'

Answer 2

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

Catalog views:

We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.

Purely personal, but I don't like the INFORMATION_SCHEMA views