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.

Ene

Answer 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'

Peddada

The Information Schema are available in SQL Server 2000 as well. – Mireille

And in Postgres, at from 8.0 (did not check in older versions) – Fractious

Answer 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]')

Dahlgren

Answer 3

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]'

Switchback

Answer 4

For those looking for an answer in Oracle:

SELECT column_name FROM user_tab_columns WHERE table_name = 'TABLENAME'

Abydos

Answer 5

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)

Wilmott

Answer 6

Just for completeness, since MySQL and Postgres have already been mentioned: With SQLite, use "pragma table_info()"

sqlite> pragma table_info('table_name');
cid         name        type        notnull     dflt_value  pk        
----------  ----------  ----------  ----------  ----------  ----------
0           id          integer     99                      1         
1           name                    0                       0         

Soleure

Answer 7

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.

Leonialeonid

Answer 8

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'

Caricature

Answer 9

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'

Uteutensil

Answer 10

MySQL is the same:

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tablename'

Foot

Answer 11

For IBM DB2 (will double check this on Monday to be sure.)

SELECT TABNAME,COLNAME from SYSCAT.COLUMNS where TABNAME='MYTABLE'

Annoyance

Answer 12

MySQL

describe tablename

Warship

Answer 13

select COLUMN_NAME1,COLUMN_NAME2 from SCHEMA_NAME.TABLE_NAME where TABLE_NAME.COLUMN_NAME = 'COLUMN_NAME1';

Loosetongued