Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    Unanswered: How to check in sql, if field <SomeField> is part of the key for table <SomeTable>

    I need to write sql expression that will
    check if field <SomeField> is part of the key for table <SomeTable>.
    Please advice.
    Thanks in advance.

    John Smith

  2. #2
    Join Date
    Jul 2002
    Posts
    3
    The following stored procedure is long but one way of checking if a <SOMECOLUMN> is part of key/index to a <SOMETABLE> or NOT. It basically uses Sybase index_col function to get column names from an index/key and syscols system table to get total number of columns in table.
    The parameters to the stored procedure are the table name and column name. It returns '0' if the column is a key and '1' if not


    use tempdb
    go

    create proc Is_Col_Key(@tablename varchar(30), @columnname varchar(30))
    as

    set nocount on
    declare @totindex integer, @totcols integer, @temptotcols integer, @indexcolumn varchar(30)
    select @totcols = max(colid) from syscolumns C, sysobjects O
    where O.name = '@tablename' and C.id = O.id
    select @temptotcols = @totcols
    select @totindex = count(*) from sysindexes I, sysobjects O
    where O.name = '@tablename' and I.id = O.id and O.type = 'U'
    while (@totindex >= 1)
    begin
    select @totcols = @temptotcols
    while (@totcols > 0)
    begin
    select @indexcolumn = index_col('@tablename', @totindex, @totcols)
    if (@indexcolumn = @columnname)
    return 0
    select @totcols = @totcols -1
    end
    select @totindex = @totindex - 1
    end
    return 1

    go


    waavman
    Last edited by waavman; 08-01-02 at 18:43.

Posting Permissions

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