Recipe5.2.Listing a Table s Columns

Recipe 5.2. Listing a Table's Columns

Problem

You want to list the columns in a table, along with their data types, and their position in the table they are in.

Solution

The following solutions assume that you wish to list columns, their data types, and their numeric position in the table named EMP in the schema SMEAGOL.

DB2

Query SYSCAT.COLUMNS:

 1 select colname, typename, colno 2   from syscat.columns 3  where tabname   = 'EMP' 4    and tabschema = 'SMEAGOL' 

Oracle

Query ALL_TAB_COLUMNS:

 1 select column_name, data_type, column_id 2   from all_tab_columns 3  where owner      = 'SMEAGOL' 4    and table_name = 'EMP' 

PostgreSQL, MySQL, and SQL Server

Query INFORMATION_SCHEMA.COLUMNS:

 1 select column_name, data_type, ordinal_position 2   from information_schema.columns 3  where table_schema = 'SMEAGOL' 4    and table_name   = 'EMP' 

Discussion

Each vendor provides ways for you to get detailed information about your column data. In the examples above only the column name, data type, and position are returned. Additional useful items of information include length, nullability, and default values.