Hi Bennie,
You may be able to get the information you need by joining the table
functions which are loaded by the databaseMetaData tool introduced in
Derby version 10.10.1:
http://db.apache.org/derby/docs/10.10/tools/rtoolsoptdbmetadata.html
I have included a script which shows how to get some type information
for index, primary, foreign key, and unique key columns.
Hope this helps,
-Rick
connect 'jdbc:derby:memory:db;create=true';
create table t( a int, b int, c int, primary key ( a, b ) );
create index t_idx on t( c, b );
create table s( x int, y int, z int, foreign key( x, y ) references t(
a, b ), unique( y, z ) );
-- load the databaseMetaData table functions
call syscs_util.syscs_register_tool( 'databaseMetaData', true );
-- list out the columns and their types for all indexes on T
select
i.table_schem, i.table_name, i.index_name, i.ordinal_position,
c.column_name, c.data_type, c.column_size
from
table( getIndexInfo( null, 'APP', 'T', false, false ) ) i,
table( getColumns( null, '%', '%', '%' ) ) c
where i.table_schem = c.table_schem
and i.table_name = c.table_name
and i.column_name = c.column_name
order by i.table_schem, i.table_name, i.index_name, i.ordinal_position;
-- list out the columns and their types for the primary key on T
select
p.table_schem, p.table_name, p.pk_name, p.key_seq,
c.column_name, c.data_type, c.column_size
from
table( getPrimaryKeys( null, 'APP', 'T' ) ) p,
table( getColumns( null, '%', '%', '%' ) ) c
where p.table_schem = c.table_schem
and p.table_name = c.table_name
and p.column_name = c.column_name
order by p.table_schem, p.table_name, p.pk_name, p.key_seq;
-- list out the columns and their types for all the foreign keys on S
select distinct
k.fktable_schem, k.fktable_name, k.fk_name, k.key_seq,
c.column_name, c.data_type, c.column_size
from
table( getImportedKeys( null, 'APP', 'S' ) ) k,
table( getColumns( null, '%', '%', '%' ) ) c
where k.fktable_schem = c.table_schem
and k.fktable_name = c.table_name
and k.fkcolumn_name = c.column_name
order by k.fktable_schem, k.fktable_name, k.fk_name, k.key_seq;
-- list out the columns and their types for the unique keys on S
select
i.table_schem, i.table_name, i.index_name, i.ordinal_position,
c.column_name, c.data_type, c.column_size
from
table( getIndexInfo( null, 'APP', 'S', false, false ) ) i,
table( getColumns( null, '%', '%', '%' ) ) c,
sys.sysconstraints sc
where i.table_schem = c.table_schem
and i.table_name = c.table_name
and i.column_name = c.column_name
and i.index_name = sc.constraintName
and sc.type = 'U'
order by i.table_schem, i.table_name, i.index_name, i.ordinal_position;
-- unload the databaseMetaData table functions
call syscs_util.syscs_register_tool( 'databaseMetaData', false );
On 12/5/13 10:33 PM, True||False wrote:
Hi,
I'm trying to retrieve the columns for constraints (PK, FK, unique,
check) from system tables and looking through the documentation I
cannot see how I can get that information. SYS.SYSCONSTRAINTS does not
contain column information. I normally would expect something like
SYS.CONSTRAINTCOLUMNS.
For example,I want to know which column belong to for the primary key.
I find the tables in sys schema then I can get all the primary key
info. The SQL is:
|select t.tablename,
conglomeratename backIdxName,
cst.constraintname,
cst.type
from sys.systables t,
sys.sysconstraints cst,
sys.sysconglomerates cgl,
sys.syskeys sk
where isindex= 'TRUE'
and cgl.tableid= t.tableid
and (sk.constraintid= cst.constraintidand cst.type= 'P' and
sk.conglomerateid= cgl.conglomerateid)
and t.tableid= cst.tableid
and t.tabletype= 'T'|
All the primary key is query out, but I wanna know which column belong
to for the primary key. There's no column info in sys.sysconstraints..
I must be missing something obvious here. The same is true for
indexes. I cannot find any information regarding them either in the
system tables. Is there any other way to retrieve the information?
java.sql.DatabaseMetadata only does part of the job.
Thanks in advance,
Bennie.
