I have some Sybase ASE tables with primary and foreign keys and I want to get list of REAL PK and FK for these tables. And this information should be returned via SELECT query to system tables. All queries which uses queries to 'syskeys' are not correct since syskeys contains only logical references for tables which is created via sp_foreignkey. For example I have below tables:
create table tbl_pk1
(col1 int primary key,
col2 int);
create table tbl_pk3
(col1 int null,
col2 int);
sp_primarykey 'tbl_pk3', 'col1'
And below query will return only 'tbl_pk3'.
select t.name from syskeys i INNER JOIN sysobjects t ON i.id = t.id where t.name in ('tbl_pk1', 'tbl_pk3')
I know what 'sp_helpconstraint' procedure can help me, but I can't call it in SELECT. Maybe someone can help me with query to system tables to get actual information about primary and foreign keys for tables?
Here are how it can be done
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 1) column_name, 1 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 1) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 2) column_name, 2 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 2) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 3) column_name, 3 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 3) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 4) column_name, 4 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 4) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 5) column_name, 5 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 5) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 6) column_name, 6 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 6) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 7) column_name, 7 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 7) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 8) column_name, 8 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 8) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 9) column_name, 9 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 9) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 10) column_name, 10 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 10) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 11) column_name, 11 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 11) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 12) column_name, 12 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 12) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 13) column_name, 13 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 13) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 14) column_name, 14 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 14) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 15) column_name, 15 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 15) is not null union
select user_name(o.uid) user_name, object_name(i.id) table_name, index_col(object_name(i.id), i.indid, 16) column_name, 16 column_id, i.name as con_name
from sysindexes i, sysobjects o where object_name(i.id) = 'tbl_pk_mult' and user_name(o.uid) = 'dbo' and status & 2048 = 2048 and i.id = o.id and index_col(object_name(i.id), i.indid, 16) is not null