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

User · Answer

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

User · Answer

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

User · Answer

For SQL Server  sp help tablename

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For MS SQL Server   select   from information schema columns where table name    tableName

User · Answer

MS SQL Server   sp columns  tablename

User · Answer

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

User · Answer

For Oracle  PL SQL   SELECT column name FROM user tab cols WHERE table name    myTableName    For MySQL  SHOW COLUMNS FROM table name

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

Examples related to sql

Examples related to mysql

Examples related to sql-server

Examples related to database

Examples related to oracle