Is there a succinct way to retrieve a list of table column names with T-SQL?
sql-server, t-sql
Solution
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'
Problem
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)?