On 04/10/10 05:29 PM, Ян Программист wrote: > From my previous post: > > I created following DDL: > > create table server_shutdown_type > ( > id integer(3) not null, > name varchar(10) not null, > primary key(id) > Ru); > create table server_shutdown_log > ( > record_id integer not null, > shutdown_type integer not null, > admin_records VARCHAR(30), > primary key (record_id), > foreign key (shutdown_type) references server_shutdown_type (id) > ); > > select SYS.SYSCOLUMNS.COLUMNNAME, SYS.SYSCOLUMNS.COLUMNDATATYPE, > SYS.SYSCONSTRAINTS.TYPE, SYS.SYSCONSTRAINTS.REFERENCECOUNT from > SYS.SYSCOLUMNS inner join SYS.SYSTABLES on SYS.SYSTABLES.TABLEID = > SYS.SYSCOLUMNS.REFERENCEID INNER JOIN SYS.SYSCONSTRAINTS on > SYS.SYSCOLUMNS.REFERENCEID = SYS.SYSCONSTRAINTS.TABLEID where > SYS.SYSTABLES.TABLENAME like 'SERVER%'; > COLUMNNAME > |COLUMNDATATYPE > |&|REFERENCEC& > -------------------------------------------------------------------------------------------------------------------------------------------------------------- > ADMIN_RECORDS > |VARCHAR(30) |P|0 > > ADMIN_RECORDS > |VARCHAR(30) |F|0 > > RECORD_ID > |INTEGER NOT NU&|P|0 > > RECORD_ID > |INTEGER NOT NU&|F|0 > > SHUTDOWN_TYPE > |INTEGER NOT NU&|P|0 > > SHUTDOWN_TYPE > |INTEGER NOT NU&|F|0 > > ID > |INTEGER NOT NU&|P|1 > > NAME > |VARCHAR(10) NO&|P|1 > > Hence columns NAME, ADMIN_RECORDS appear with 'P' value for > SYS.SYSCONSTRAINTS.TYPE. But I haven't forced those to be primary keys > in DDL. John
I think the above join only tells that those columns are in a table that has a primary key and/or foreign key defined. To find out which columns are part of a key, I think you need to inspect the IndexDescriptor object in SYS.SYSCONGLOMERATES.DESCRIPTOR, but I'm not sure if you can do that from SQL. -- Knut Anders
