6

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

Community
  • 1
  • 1
Matthew Murdoch
  • 30,874
  • 30
  • 96
  • 127

2 Answers2

9

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'
Matthew Murdoch
  • 30,874
  • 30
  • 96
  • 127
David M
  • 71,481
  • 13
  • 158
  • 186
4
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
  • 422,506
  • 82
  • 585
  • 676
  • 1
    The INFORMATION_SCHEMA views were designed by a committee - that says it all :-( Huge, ugly, not very useful.... – marc_s Jan 06 '10 at 15:53
  • @marc_s: he he. Quite cynical,no? – gbn Jan 06 '10 at 16:11
  • 1
    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 Jan 07 '10 at 06:59