Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    35

    Unanswered: about primary keys

    i'm currently self-studying ase 12.5

    i learned that you can actually get information about your database(tables, columns, constraints etc) using sql statements through your system tables, i just couldn't figure how i can determine a table's primary keys using the system tables...

    thanks in advance! it's much better if you can post a sample sql statement, thnx!

  2. #2
    Join Date
    Jun 2004
    Location
    Paris, France
    Posts
    43

    Thumbs up

    hi
    if you want to recreate the keys of a table,
    you can use this script which will explain you where the informations are :

    -- recreate the primary key first, because other keys may have to use it

    select "sp_primarykey " + @table + "," +
    (select name from syscolumns where id = k.id and colid = k.key1) +
    case when k.keycnt >= 2 then (select "," + name from syscolumns where id = k.id and colid = k.key2) else "" end +
    case when k.keycnt >= 3 then (select "," + name from syscolumns where id = k.id and colid = k.key3) else "" end +
    case when k.keycnt >= 4 then (select "," + name from syscolumns where id = k.id and colid = k.key4) else "" end +
    case when k.keycnt >= 5 then (select "," + name from syscolumns where id = k.id and colid = k.key5) else "" end +
    case when k.keycnt >= 6 then (select "," + name from syscolumns where id = k.id and colid = k.key6) else "" end +
    case when k.keycnt >= 7 then (select "," + name from syscolumns where id = k.id and colid = k.key7) else "" end +
    case when k.keycnt = 8 then (select "," + name from syscolumns where id = k.id and colid = k.key8) else "" end + char(13) + char(10) + 'go'
    from syskeys k
    where k.id = object_id(@table)
    and
    k.type = 1


    select "sp_" +
    case k.type
    when 2 then "foreign"
    when 3 then "common"
    end +
    "key " + o1.name + "," + o2.name + "," +
    (select name from syscolumns where id = k.id and colid = k.key1) +
    case when k.keycnt >= 2 then (select "," + name from syscolumns where id = k.id and colid = k.key2) else "" end +
    case when k.keycnt >= 3 then (select "," + name from syscolumns where id = k.id and colid = k.key3) else "" end +
    case when k.keycnt >= 4 then (select "," + name from syscolumns where id = k.id and colid = k.key4) else "" end +
    case when k.keycnt >= 5 then (select "," + name from syscolumns where id = k.id and colid = k.key5) else "" end +
    case when k.keycnt >= 6 then (select "," + name from syscolumns where id = k.id and colid = k.key6) else "" end +
    case when k.keycnt >= 7 then (select "," + name from syscolumns where id = k.id and colid = k.key7) else "" end +
    case when k.keycnt = 8 then (select "," + name from syscolumns where id = k.id and colid = k.key8) else "" end + char(13) + char(10) + 'go'
    from syskeys k,
    sysobjects o1,
    sysobjects o2
    where (
    o1.name = @table
    or
    o2.name = @table
    )
    and
    k.type in (2,3)
    and
    k.id = o1.id
    and
    k.depid = o2.id

  3. #3
    Join Date
    May 2004
    Posts
    35
    just wonderin, what's the difference of sp_pkey and sp_primarykey?

  4. #4
    Join Date
    Jun 2004
    Location
    Paris, France
    Posts
    43
    ... 6 letters ?


    i just checked 'sp_pkey', (just in case) but i haven't found it... neither on 11.9.2, nor on 12.5.03

  5. #5
    Join Date
    May 2004
    Posts
    35
    syskeys table
    All databases - contains one row for each primary, foreign, or common key.

    i got around 200 tables with more than 8 primary keys in some tables.

    how come my syskeys table has around 30 records only?

    all tables i'm refering is in one database btw.

    many thanks!

    and btw

    the sp_keys i'm refering to from my last post is located in my sybsystemprocs database:


    /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
    /*
    ** note: there is one raiserror message: 18040
    **
    ** messages for "sp_pkeys" 18039, 18040
    **
    ** 17461, "Object does not exist in this database."
    ** 18039, "table qualifier must be name of current database."
    ** 18040, "catalog procedure %1! can not be run in a transaction.", sp_pkeys
    **
    */

    create procedure sp_pkeys
    @table_name varchar(32),
    @table_owner varchar(32) = null,
    @table_qualifier varchar(32) = null as
    declare @keycnt smallint
    declare @indexid smallint
    declare @i int
    declare @id int
    declare @uid int
    select @id = NULL



    set nocount on

    if (@@trancount = 0)
    begin
    set chained off
    end
    else
    begin
    /* if inside a transaction */
    /* catalog procedure sp_pkeys can not be run in a transaction.*/
    raiserror 18040, "sp_pkeys"
    return (1)
    end

    set transaction isolation level 1

    if @table_qualifier is not null

    begin
    if db_name() != @table_qualifier
    begin
    /* if qualifier doesn't match current database */
    /* "table qualifier must be name of current database"*/
    raiserror 18039
    return (1)
    end
    end

    if @table_owner is null

    begin
    select @id = id , @uid = uid
    from sysobjects
    where name = @table_name
    and uid = user_id()
    if (@id is null)
    begin
    select @id = id ,@uid = uid
    from sysobjects
    where name = @table_name
    and uid = 1
    end
    end
    else
    begin
    select @id = id , @uid = uid
    from sysobjects
    where name = @table_name and uid = user_id(@table_owner)
    end

    if (@id is null)
    begin
    /* 17461, "Object does not exist in this database." */
    raiserror 17461
    return (1)
    end

    create table #pkeys(
    table_qualifier varchar(32),
    table_owner varchar(32),
    table_name varchar(32),
    column_name varchar(32),
    key_seq smallint)


    /*
    ** now we search for primary key (only declarative) constraints
    ** There is only one primary key per table.
    */

    select @keycnt = keycnt, @indexid = indid
    from sysindexes
    where id = @id
    and 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 */

    /*
    ** For non-clustered indexes, keycnt as returned from sysindexes is one
    ** greater than the actual key count. So we need to reduce it by one to
    ** get the actual number of keys.
    */

    if (@indexid >= 2)
    begin
    select @keycnt = @keycnt - 1
    end

    select @i = 1

    while @i <= @keycnt
    begin
    insert into #pkeys values
    (db_name(), user_name(@uid), @table_name,
    index_col(@table_name, @indexid, @i, @uid), @i)
    select @i = @i + 1
    end

    select table_qualifier, table_owner, table_name, column_name, key_seq
    from #pkeys
    order by table_qualifier, table_owner, table_name, key_seq

    return (0)

  6. #6
    Join Date
    May 2004
    Posts
    35
    bump!


    i believe the above script is from sybase and not a custom one

    so can nybody help me, if nobody can help me, well in that case, can nybody tell me how Sybase Central does the "Generate DLL" command when i right click a table, the script or the pseudocode perhaps? that way i can study how sybase central does that, and prove that there is a way to get the primary keys of tables

    many many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •