Getting DB Design of a DB. Please help.

smokkyydesperado · #1 (**permalink**) 07-15-10, 03:19

I have a DB server which has a database. say . A and inside the database there is an active schema. S which contains around 100 tables ( with relations )

I want to extract DB design(in excel format ) of this table which will contain the following information ( as columns and sub-columns)

Table Name
Table Description
No: Of Parents
No: Of Children
Column
Name Type Length Nulls Default
Primary Key
Name Column
UNIQUE KEY
Name Column
Foreign Key
Name Column Parent Key Parent Table
Delete Rule
Update Rule
Check Constraint

How can i get this without manual work?. Is there any query to retrieve this kind of information or Can i use any tool for getting the same?

Would be most obliged if someone can help me out.
Thanks in advance.

singhipst · #2 (**permalink**) 07-15-10, 04:34

you could execute query againest the metadata to retrive desired result.
Metadata catalog tables/view you could use SYSIBM.SYSTABLES A,SYSIBM.SYSCOLUMNS,syscat.tabconst, syscat.keycoluse, syscat.references

For Example:

Code:

select B.TBNAME,B.NAME,B.COLTYPE,B.LONGLENGTH,B.NULLS from SYSIBM.SYSTABLES A,SYSIBM.SYSCOLUMNS B WHERE A.NAME=B.TBNAME AND A.TYPE='T'

have a look to below link also post

Query to count number of coulmns in constraints

przytula_guy · #3 (**permalink**) 07-16-10, 06:19

how about db2look
db2look - DB2 Statistics and DDL Extraction Tool - IBM DB2 9.7 for Linux, UNIX, and Windows
or create a custom query if table is needed

Internet Services

Web Development

Digital Marketing

Consumer Tech