What is the SQL command to return the field names of a table?
Solution 1
MySQL 3 and 4 (and 5):
desc tablename
which is an alias for
show fields from tablename
SQL Server (from 2000) and MySQL 5:
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'tablename'
Completing the answer: like people below have said, in SQL Server you can also use the stored procedure sp_help
exec sp_help 'tablename'
Solution 2
SQL-92 standard defines INFORMATION_SCHEMA which conforming rdbms's like MS SQL Server support. The following works for MS SQL Server 2000/2005/2008 and MySql 5 and above
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'myTable'
MS SQl Server Specific:
exec sp_help 'myTable'
This solution returns several result sets within which is the information you desire, where as the former gives you exactly what you want.
Also just for completeness you can query the sys tables directly. This is not recommended as the schema can change between versions of SQL Server and INFORMATION_SCHEMA is a layer of abstraction above these tables. But here it is anyway for SQL Server 2000
select [name] from dbo.syscolumns where id = object_id(N'[dbo].[myTable]')
Solution 3
For those looking for an answer in Oracle:
SELECT column_name FROM user_tab_columns WHERE table_name = 'TABLENAME'
Solution 4
PostgreSQL understands the
select column_name from information_schema.columns where table_name = 'myTable'
syntax. If you're working in the psql shell, you can also use
\d myTable
for a description (columns, and their datatypes and constraints)
Solution 5
You can use the provided system views to do this:
eg
select * from INFORMATION_SCHEMA.COLUMNS
where table_name = '[table name]'
alternatively, you can use the system proc sp_help
eg
sp_help '[table name]'
Related videos on Youtube
WalkingRandomly
I love mathematics and mathematical software. Author of www.walkingrandomly.com
Updated on November 26, 2020Comments
-
WalkingRandomly over 3 years
Say I have a table called myTable. What is the SQL command to return all of the field names of this table? If the answer is database specific then I need SQL Server right now but would be interested in seeing the solution for other database systems as well.
-
Martynnw over 15 yearsThe Information Schema are available in SQL Server 2000 as well.
-
Mureinik almost 9 yearsAnd in Postgres, at from 8.0 (did not check in older versions)