2

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user109447
  • 1,069
  • 1
  • 10
  • 20
  • Could you please show how you would like your result to look like? Grouping will not provide the column names etc. so you might want something else - showing an example would help us helping you – MichaelTiefenbacher Oct 04 '18 at 12:22
  • I would like to have list of column names with description (type, length) for each table from given schema. – user109447 Oct 04 '18 at 12:24
  • https://stackoverflow.com/questions/3583963/db2-query-to-retrieve-all-table-names-for-a-given-schema/52322317#52322317 – Paul Vernon Oct 05 '18 at 20:58

3 Answers3

2

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;
Matthew Emmerton
  • 29
  • 2
  • 1
    Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Oct 04 '18 at 12:35
  • Why soulution above return non distinct values? – user109447 Oct 05 '18 at 00:21
1

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
MichaelTiefenbacher
  • 3,805
  • 2
  • 11
  • 17
0

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;
Narayan Yerrabachu
  • 1,714
  • 1
  • 19
  • 31