Is there any select statement to return the list of columns in the table?
+8
A:
SELECT Column_Name + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table_Name'
Paul Rowland
2009-04-08 00:50:30
+1
A:
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: http://stackoverflow.com/questions/723115/how-do-i-look-at-column-metadata-in-sybase/723577#723577
tpdi
2009-04-08 00:52:13
My answer is right for SQL Server 2005 and SQL Server 2008 as well. (Dont have SQL2000 to check against)
Paul Rowland
2009-04-08 00:54:07
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
2009-04-08 00:55:05
@Paul, I can verify it works on 2000 as well.
Blorgbeard
2009-04-08 00:56:48
+1
A:
sp_help TableName
Will give you all columns, plus lots of other information.
Andy Jones
2009-04-08 08:50:10
A:
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. [email protected]