Knut Anders Hatlen wrote:
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.
It might be possible to use a table function to unpack the descriptor.
Hope this helps,
-Rick
