[sql] How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

SQL Server

To list all the user defined tables of a database:

use [databasename]
select name from sysobjects where type = 'u'

To list all the columns of a table:

use [databasename]
select name from syscolumns where id=object_id('tablename')

For MySQL, use:

DESCRIBE name_of_table;

This also works for Oracle as long as you are using SQL*Plus, or Oracle's SQL Developer.

Microsoft SQL Server Management Studio 2008 R2:

In a query editor, if you highlight the text of table name (ex dbo.MyTable) and hit ALT+F1, you'll get a list of column names, type, length, etc.

ALT+F1 while you've highlighted dbo.MyTable is the equivalent of running EXEC sp_help 'dbo.MyTable' according to this site

I can't get the variations on querying INFORMATION_SCHEMA.COLUMNS to work, so I use this instead.

Example:

select Table_name as [Table] , column_name as [Column] , Table_catalog as [Database], table_schema as [Schema]  from information_schema.columns
where table_schema = 'dbo'
order by Table_name,COLUMN_NAME

Just my code

Just a slight correction on the others in SQL Server (schema prefix is becoming more important!):

SELECT name
  FROM sys.columns 
  WHERE [object_id] = OBJECT_ID('dbo.tablename');

MS SQL Server:

sp_columns [tablename]

The following code worked very well for me:

SELECT
       o.name as tableName,
       c.name as columnName,
       o.[type],
       s.name as schemaName,
       o.type_desc
FROM
       sys.objects AS o
       INNER JOIN sys.[columns] AS c ON c.[object_id] = o.[object_id]
       INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
WHERE
       o.type_desc='USER_TABLE' 
       AND c.name='YourColumnName' --if comment this line,show all columns
ORDER BY
        o.name,
        c.column_id

you just replace your Column name with 'YourColumnName'. then run query

For Oracle (PL/SQL)

SELECT column_name
FROM user_tab_cols
WHERE table_name = 'myTableName'

For MySQL

SHOW COLUMNS FROM table_name

For MS SQL Server:

select * from information_schema.columns where table_name = 'tableName'

SQL Server

SELECT 
    c.name 
FROM
    sys.objects o
INNER JOIN
    sys.columns c
ON
    c.object_id = o.object_id
AND o.name = 'Table_Name'

or

SELECT 
    COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME  = 'Table_Name'

The second way is an ANSI standard and therefore should work on all ANSI compliant databases.

(5 years laters, for the Honor of PostgreSQL, the most advanced DDBB of the Kingdom)

In PostgreSQL:

\d table_name

Or, using SQL:

select column_name, data_type, character_maximum_length
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = 'table_name';

For SQL Server

sp_help tablename

I know it's late but I use this command for Oracle:

select column_name,data_type,data_length from all_tab_columns where TABLE_NAME = 'xxxx' AND OWNER ='xxxxxxxxxx'