3

Is there any select statement to return the list of columns in the table?

Paul Rowland
  • 8,244
  • 12
  • 55
  • 76
Vinod
  • 31,933
  • 35
  • 96
  • 119

4 Answers4

10

The INFORMATION_SCHEMA.COLUMNS view will provide the column names for a particular table name.

SELECT Column_Name + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table_Name'

There are several other views as well as the one above which you may find useful. These INFORMATION_SCHEMA views provide information on the schema of your database. 

Select top 10 * from Information_Schema.tables 
Select top 10 * from Information_Schema.views 
Select top 10 * from Information_Schema.routines 
Select top 10 * from Information_Schema.parameters
Paul Rowland
  • 8,244
  • 12
  • 55
  • 76
1

Paul's answer is right for mysql. ON EDIT: and sql server too, apparently. Arrgh. Sorry Paul.

For sql server, you want sys.syscolumns, very similarly to this answer: How do I look at column metadata in Sybase?

Community
  • 1
  • 1
tpdi
  • 34,554
  • 11
  • 80
  • 120
  • My answer is right for SQL Server 2005 and SQL Server 2008 as well. (Dont have SQL2000 to check against) – Paul Rowland Apr 08 '09 at 00:54
  • The INFORMATION_SCHEMA schema is the officially supported set of system views for inspecting database, table, column, and object information. syscolumns, sysobjects, etc. are all system objects and Microsoft doesn't guarantee their continued existence or consistent formatting. – Adam Robinson Apr 08 '09 at 00:55
1

sp_help TableName

Will give you all columns, plus lots of other information.

Andy Jones
  • 1,472
  • 9
  • 15
0

You can also get column data in SqlServer 2005 using

SELECT column_name 'Column Name', data_type 'Data Type' FROM information_schema.columns WHERE table_name = 'table name'

Srinivas Dontula. sdonthula@live.com