How do I list all the columns in a table?

Question

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

Answer 1

For Oracle (PL/SQL)

SELECT column_name
FROM user_tab_cols
WHERE table_name = 'myTableName'

For MySQL

SHOW COLUMNS FROM table_name

Answer 2

DESCRIBE name_of_table;

Answer 3

MS SQL Server:

sp_columns [tablename]

Answer 4

For MS SQL Server:

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

Answer 5

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.

Answer 6

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

Answer 7

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');