What is the Select statement to return the column

问题:

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

回答1:

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

回答2:

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?

回答3:

sp_help TableName

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

回答4:

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. [e-mail]