ASE Database forum - sp_pkeys and sp

Author

sp_pkeys and sp_fkeys

Frank Rizzo

2005-10-27, 8:21 am

Mssql has some handy extra columns coming from these 2 sprocs. One of
the more useful ones is the name of the primary (or the foreign) key
that the column belongs to. Another one is the sequence number of the
column in the primary key (in the case of sp_pkeys).

How can I replicate this functionality in ASE? I can't figure out how
to find the name of the primary key (or foreign key) based on the output
of the sp_pkeys and sp_fkeys sprocs.

Thanks.

Paul

2005-10-27, 8:21 am

Hi Frank,

What version of ASE? I am finding that the CSPs for the OEM do not provide
for the Primary key name. I am not sure why the developers from long ago
never did this and to be honest no one had ever ask about this. So that
Driver returns NULL for these values.

On the newer inhouse built drivers, the CSPs DO return the PKey name and
Foreign Keys. Also a caveat - create the keys with the Create table
statement and not use sp_primarykey or sp_foreignkey if you want the CSP to
work. The create table use of setting the keys complies more along the
lines of the SQL standards.

For the OEM drivers, it might be a good idea to pursue that - I think mostly
folks wanted the table and column names for this stuff, so the original CSP
designers never added the pk_name to the output.

On the CSPs for the OEM driver (DataDirect, SYODASE.DLL, Sybase ASE ODBC
Driver) it might be possible for us to add the column - I know the driver
has a field for these items. If you want to pursue this open a support case
and we can try and push it through.

-Paul

"Frank Rizzo" <none@none.net> wrote in message news:4345d189@forums
-2-dub...
> Mssql has some handy extra columns coming from these 2 sprocs. One of
> the more useful ones is the name of the primary (or the foreign) key
> that the column belongs to. Another one is the sequence number of the
> column in the primary key (in the case of sp_pkeys).
>
> How can I replicate this functionality in ASE? I can't figure out how
> to find the name of the primary key (or foreign key) based on the output
> of the sp_pkeys and sp_fkeys sprocs.
>
> Thanks.

Frank Rizzo

2005-10-27, 8:21 am

Paul,

I want to retrieve the information independent of drivers (since I don't
know what the customer will have). I am looking for information on how
to retrieve this information either from the system tables or using the
system stored procs. I am currently trying to retrieve it from ASE 12.5.3

Regards

Paul wrote:
> Hi Frank,
>
> What version of ASE? I am finding that the CSPs for the OEM do not provide
> for the Primary key name. I am not sure why the developers from long ago
> never did this and to be honest no one had ever ask about this. So that
> Driver returns NULL for these values.
>
> On the newer inhouse built drivers, the CSPs DO return the PKey name and
> Foreign Keys. Also a caveat - create the keys with the Create table
> statement and not use sp_primarykey or sp_foreignkey if you want the CSP to
> work. The create table use of setting the keys complies more along the
> lines of the SQL standards.
>
> For the OEM drivers, it might be a good idea to pursue that - I think mostly
> folks wanted the table and column names for this stuff, so the original CSP
> designers never added the pk_name to the output.
>
> On the CSPs for the OEM driver (DataDirect, SYODASE.DLL, Sybase ASE ODBC
> Driver) it might be possible for us to add the column - I know the driver
> has a field for these items. If you want to pursue this open a support case
> and we can try and push it through.
>
> -Paul
>
> "Frank Rizzo" <none@none.net> wrote in message news:4345d189@forums
-2-dub...
>
>
>
>

2005-10-27, 8:21 am

Look at the source code for sp_pkeys, sp_fkeys.

Here is a quick and dirty SQL to list the primary keys
/columns in a database. The foreign keys would be a little
more complex.

create table #pkeys (
id int
, TableName varchar(30) null
, index_name varchar(30) null
, KeySeq int
, ColName varchar(30) null
)
declare @keycnt int
select @keycnt = 1
while @keycnt < 17
begin
insert #pkeys
select id
, object_name(id)
, name
, @keycnt
, index_col(object_nam
e(id), indid, @keycnt)
from sysindexes
where indid > 0 /* make sure it is an index */
and status2 & 2 = 2 /* make sure it is a declarative
constr */
and status & 2048 = 2048 /* make sure it is a
primary key */
and index_col(object_nam
e(id), indid, @keycnt) is
not null
select @keycnt = @keycnt + 1
end

update #pkeys set TableName = space(30), index_name =
space(30)
where KeySeq > 1
select TableName , index_name , KeySeq , ColName from
#pkeys order by id

drop table #pkeys

[color=darkred]
> Paul,
>
> I want to retrieve the information independent of drivers
> (since I don't know what the customer will have). I am
> looking for information on how to retrieve this
> information either from the system tables or using the
> system stored procs. I am currently trying to retrieve it
> from ASE 12.5.3
>
> Regards
>
>
> Paul wrote:
> work. The create table use of setting the keys complies
> sprocs. One of >>the more useful ones is the name of the
> primary (or the foreign) key >>that the column belongs to.
> Another one is the sequence number of the >>column in the
> primary key (in the case of sp_pkeys). >>
> figure out how >>to find the name of the primary key (or
> foreign key) based on the output >>of the sp_pkeys and
> sp_fkeys sprocs. >>

Frank Rizzo

2005-10-27, 8:21 am

Thank you, this is exactly what I needed for the primary key. Worked
right out of the box. Now I just gotta figure out the foreign key
equivalent.

Thanks again.
Regards.

wk wrote:[color=darkred
]
> Look at the source code for sp_pkeys, sp_fkeys.
>
> Here is a quick and dirty SQL to list the primary keys
> /columns in a database. The foreign keys would be a little
> more complex.
>
>
> create table #pkeys (
> id int
> , TableName varchar(30) null
> , index_name varchar(30) null
> , KeySeq int
> , ColName varchar(30) null
> )
> declare @keycnt int
> select @keycnt = 1
> while @keycnt < 17
> begin
> insert #pkeys
> select id
> , object_name(id)
> , name
> , @keycnt
> , index_col(object_nam
e(id), indid, @keycnt)
> from sysindexes
> where indid > 0 /* make sure it is an index */
> and status2 & 2 = 2 /* make sure it is a declarative
> constr */
> and status & 2048 = 2048 /* make sure it is a
> primary key */
> and index_col(object_nam
e(id), indid, @keycnt) is
> not null
> select @keycnt = @keycnt + 1
> end
>
> update #pkeys set TableName = space(30), index_name =
> space(30)
> where KeySeq > 1
> select TableName , index_name , KeySeq , ColName from
> #pkeys order by id
>
> drop table #pkeys
>
>
>

2005-10-27, 8:21 am

Basic foreign key data.

create table #fkeys (
id int
, constrid int
, TableName varchar(30) null
, refdb varchar(30) null
, reftable varchar(30) null
, ColName varchar(30) null
, RefColName varchar(30) null
, KeySeq int
)
insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey1, pmrydbid)
, col_name(reftabid, refkey1, frgndbid)
, 1
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey2, pmrydbid)
, col_name(reftabid, refkey2, frgndbid)
, 2
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey3, pmrydbid)
, col_name(reftabid, refkey3, frgndbid)
, 3
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey4, pmrydbid)
, col_name(reftabid, refkey4, frgndbid)
, 4
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey5, pmrydbid)
, col_name(reftabid, refkey5, frgndbid)
, 5
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey6, pmrydbid)
, col_name(reftabid, refkey6, frgndbid)
, 6
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey7, pmrydbid)
, col_name(reftabid, refkey7, frgndbid)
, 7
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey8, pmrydbid)
, col_name(reftabid, refkey8, frgndbid)
, 8
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey9, pmrydbid)
, col_name(reftabid, refkey9, frgndbid)
, 9
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey10, pmrydbid)
, col_name(reftabid, refkey10, frgndbid)
, 10
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey11, pmrydbid)
, col_name(reftabid, refkey11, frgndbid)
, 11
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey12, pmrydbid)
, col_name(reftabid, refkey12, frgndbid)
, 12
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey13, pmrydbid)
, col_name(reftabid, refkey13, frgndbid)
, 13
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey14, pmrydbid)
, col_name(reftabid, refkey14, frgndbid)
, 14
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey15, pmrydbid)
, col_name(reftabid, refkey15, frgndbid)
, 15
from sysreferences sr

insert #fkeys
select tableid, constrid
, object_name(tableid)

, frgndbname
, object_name(reftabid
)
, col_name(tableid, fokey16, pmrydbid)
, col_name(reftabid, refkey16, frgndbid)
, 6
from sysreferences sr

delete #fkeys where ColName is null
update #fkeys set TableName = space(30) where KeySeq > 1
insert #fkeys select distinct id, constrid, space(30),
space(30), space(30), space(30), space(30), 99 from #fkeys
if exists (select 1 from #fkeys where refdb != space(1))
begin
select TableName , ColName, refdb as 'ForgnDB', reftable,
RefColName from
#fkeys order by id, constrid, KeySeq
end
else
begin
select TableName , ColName, reftable, RefColName from
#fkeys order by id, constrid, KeySeq
end

drop table #fkeys> Thank you, this is exactly what I needed
for the primary[color=darkre
d]
> key. Worked right out of the box. Now I just gotta
> figure out the foreign key equivalent.
>
> Thanks again.
> Regards.
>
> wk wrote:
> drivers >>(since I don't know what the customer will
> have). I am >>looking for information on how to retrieve
> this >>information either from the system tables or using
> the >>system stored procs. I am currently trying to
> retrieve it >>from ASE 12.5.3
> the >>>OEM do not provide for the Primary key name. I am
> not >>>sure why the developers from long ago never did
> this and >>>to be honest no one had ever ask about this.
> So that >>>Driver returns NULL for these values.
> , >>>Sybase ASE ODBC Driver) it might be possible for us
> to >>>add the column - I know the driver has a field for
> these >>>items. If you want to pursue this open a support
> case >>>and we can try and push it through.
> the >>primary (or the foreign) key >>that the column
> belongs to. >> Another one is the sequence number of the
> can't >>

Sponsored Links