What is the SQL command to return the field names of a table

Question

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

User · Answer

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

User · Answer

This is also MySQL Specific   show fields from  tablename     this doesnt just show the table names but it also pulls out all the info about the fields

User · Answer

MySQL is the same   select COLUMN NAME from INFORMATION SCHEMA COLUMNS where TABLE NAME    tablename

User · Answer

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

User · Answer

For IBM DB2  will double check this on Monday to be sure    SELECT TABNAME COLNAME from SYSCAT COLUMNS where TABNAME  MYTABLE

User · Answer

Just for completeness  since MySQL and Postgres have already been mentioned  With SQLite  use  pragma table info     sqlite gt  pragma table info  table name    cid         name        type        notnull     dflt value  pk         ----------  ----------  ----------  ----------  ----------  ---------- 0           id          integer     99                      1          1           name                    0                       0

User · Answer

MySQL  describe tablename

User · Answer

In Sybase SQL Anywhere  the columns and table information are stored separately  so you need a join   select c column name from systabcol c         key join systab t on t table id c table id         where t table name  tablename

User · Answer

For those looking for an answer in Oracle   SELECT column name FROM user tab columns WHERE table name    TABLENAME

User · Answer

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

User · Answer

If you just want the column names  then  select COLUMN NAME from INFORMATION SCHEMA COLUMNS where TABLE NAME    tablename    On MS SQL Server  for more information on the table such as the types of the columns  use  sp help  tablename

User · Answer

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

[sql] What is the SQL command to return the field names of a table?

Examples related to sql

Examples related to mysql

Examples related to sql-server

Examples related to database