CODEWITHSUNDEEP
sqldb2
user109447
user109447

Reputation: 1119

Get column names and types grouped by column names

I have IBM DB2 database. I would like to get all column names, types length and scale grouped by table names.

To get all tables from schema XYZ:

select name
from SYSIBM.SYSTABLES
where creator = 'XYZ';

Now I can get colum descriptions for given table:

SELECT NAME, COLTYPE, LENGTH, SCALE
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'sometablename'

I would like to group it:

SELECT NAME, COLTYPE, LENGTH, SCALE
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME in (select name from SYSIBM.systables where creator = 'XYZ') 
GROUP BY table_names_from_schema_xyz;

How to do it?

Upvotes: 2

Views: 2983

Answers (3)

Narayan Yerrabachu
Narayan Yerrabachu

Reputation: 1823

Try inner join with SYSIBM.systables,probably the below example should work

   select c.colname,
            t.tabname as tables, COLTYPE, LENGTH, SCALE
    from SYSIBM.SYSCOLUMNS c
    inner join SYSIBM.systables t on 
          t.tabschema = c.tabschema and t.tabname = c.tabname
    where t.type = 'T'
    and t.tabschema = 'XYZ'

    order by c.colname;

Upvotes: 0

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

Your question can be answered only from SYSCAT.COLUMNS

select tabname, colname, typename, length, scale   
 from syscat.columns
where tabschema = 'XYZ' 
order by tabname, colno

Upvotes: 1

Matthew Emmerton
Matthew Emmerton

Reputation: 29

Grouping (in the SQL sense) only makes sense in the context of aggregation functions. I suspect what you are looking for is the output ordered by table name, then column name, so all columns of the same table are "grouped" together.

This query might work for you.

SELECT T.NAME AS TABNAME, C.NAME AS COLNAME, COLTYPE, LENGTH, SCALE
FROM SYSIBM.SYSTABLES T, SYSIBM.SYSCOLUMNS C
WHERE T.NAME = C.TBNAME
AND CREATOR = 'XYZ'
ORDER BY T.NAME, C.NAME;

Upvotes: 2

Related Questions