views:

84

answers:

2

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

+5  A: 

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'
David M
@Matthew - thanks...
David M
This has the advantage that it's an ANSI standard view, and will therefore not change when Microsoft change the underlying system tables.
Christian Hayter
@Christian: the new catalog views are also designed the same way
gbn
+3  A: 
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

gbn
The INFORMATION_SCHEMA views were designed by a committee - that says it all :-( Huge, ugly, not very useful....
marc_s
@marc_s: he he. Quite cynical,no?
gbn
I use information schema because I can remember how to write the query. The sys and object_id things seem like they were designed by the same guy who did Frankenstein. :)
Jeff Meatball Yang