Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Posts
    34

    Unanswered: Querying systables to determine primary keys

    Does anyone know how I could query the systables or perhaps use the information schema views to determine which columns of tables are the primary keys? Any help would be greatly appreciated.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    select so.name,sc.*
    from sysindexes sy
    join sysobjects so on so.id=sy.id and so.xtype='U'
    join sysindexkeys si on si.id=so.id and si.indid=sy.indid
    join syscolumns sc on sc.id=so.id and sc.colid=si.colid
    join sysobjects sop on sop.parent_obj=so.id and sop.xtype='PK' and sop.name=sy.name

  3. #3
    Join Date
    Mar 2002
    Posts
    34
    Awesome snail. Thanks a bunch.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    this one gets all of those

    use northwind
    select * from information_schema.key_column_usage

  5. #5
    Join Date
    Mar 2002
    Posts
    34
    yep. I just had to modify it a little:

    SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE LEFT(CONSTRAINT_NAME,2) = 'PK'

Posting Permissions

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