CODEWITHSUNDEEP
sqldb2
deepu
deepu

Reputation:

Description of columns in a DB2 table

How can we know the description of every column in a table(DB2) through SQL?

My data base is DB2.

Upvotes: 29

Views: 108126

Answers (8)

Fima Taf
Fima Taf

Reputation: 977

Worked for me:

select * from sysibm.columns
where table_schema = 'MY_SCHEMA'

Upvotes: 0

Peter Miehle
Peter Miehle

Reputation: 6070

select 
  tabname,
  colname,
  typename,
  length,
  scale,
  default,
  nulls,
  identity,
  generated,
  remarks,
  keyseq 
from 
  syscat.columns

Upvotes: 23

Rwly
Rwly

Reputation: 209

SELECT 
TABLE_CAT, 
TABLE_SCHEM, 
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE, 
TYPE_NAME, 
COLUMN_SIZE, 
COLUMN_TEXT
FROM "SYSIBM"."SQLCOLUMNS"
WHERE TABLE_SCHEM = 'SCHEMA' 
 AND TABLE_NAME = 'TABLE'

This is on DB2 V5R4, and is not a System Table but a SYSTEM VIEW. In case that you go nuts looking for it on the tables list.

Upvotes: 20

Michael Sharek
Michael Sharek

Reputation: 5069

describe table schema.table_name ;

DB2 Describe Command

Upvotes: 2

Arijit
Arijit

Reputation: 1674

select T1.name,T1.creator from sysibm.systables T1,sysibm.syscolumns 
T2 where T1.name=T2.tbname and T1.creator=T2.tbccreator and 
T1.creator='CREATOR NAME' and T2.name='COLUMN NAME'

Upvotes: 2

tvanharp
tvanharp

Reputation: 325

-- NOTE: the where clause is case sensitive and needs to be uppercase

select
t.table_schema as Library
,t.table_name
,t.table_type
,c.column_name
,c.ordinal_position
,c.data_type
,c.character_maximum_length as Length
,c.numeric_precision as Precision
,c.numeric_scale as Scale
,c.column_default
,t.is_insertable_into
from sysibm.tables t
join sysibm.columns c
on t.table_schema = c.table_schema
and t.table_name = c.table_name
where t.table_schema = 'MYLIB'
and t.table_name = 'MYTABLE'
order by t.table_name, c.ordinal_position

-- to get a list of all the meta tables:

select * from sysibm.tables
where table_schema = 'SYSIBM'

Upvotes: 12

Erik K.
Erik K.

Reputation: 1062

SELECT COLNAME, REMARKS FROM SYSCAT.COLUMNS WHERE TABSCHEMA = 'MYSCHEMA' AND TABNAME = 'MYTABLENAME'

Upvotes: 4

Reputation:

I work on an iSeries DB2 box (v5r4), it is a special flavor of DB2.

If you're on or connecting to an iSeries (AS/400), the link mentioned by Anton is most excellent (sorry, can't vote yet!)

Describe does not work on an iSeries, but will work with DB2 on the other platforms.

Upvotes: 2

Related Questions