Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Posts
    30

    Unanswered: What's the query to get table relationships/keys

    Hey All,
    Looking for the query I can use to get the table relationships and keys. Not sure which sys table I can access for that info...??TIA

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    sysforeignkeys, sysobjects, syscolumns

  3. #3
    Join Date
    Jul 2002
    Posts
    30
    Hey,
    I'm looking for a little bit more usefule information than those tables provide. I'd like to use the Information_schema table to get to the table keys. Using something like:
    SELECT * FROM INFORMATION_SCHEMA.Tables
    but would like to actually display the table names and the leys within it - Do you know what the syntax would be?

  4. #4
    Join Date
    Jul 2002
    Posts
    30
    In case anybody is interested, this works:
    SELECT
    tc.TABLE_NAME
    , kcu.COLUMN_NAME
    , kcu.ORDINAL_POSITION -- Position in the key
    , c.DATA_TYPE
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.TABLE_CATALOG = kcu.TABLE_CATALOG
    AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
    AND tc.TABLE_NAME = kcu.TABLE_NAME
    AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.[COLUMNS] c
    ON tc.TABLE_CATALOG = c.TABLE_CATALOG
    AND tc.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND tc.TABLE_NAME = c.TABLE_NAME
    AND kcu.COLUMN_NAME= c.COLUMN_NAME
    WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND tc.TABLE_NAME != 'dtproperties'
    ORDER BY tc.TABLE_NAME

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Those tables properly linked should give you what you want.

    select sysobjects.name Object_Name,
    syscolumns.name Column_Name,
    sysobjects2.name Referenced_Object_Name,
    syscolumns2.name Referenced_Column_Name
    from sysobjects
    inner join syscolumns on sysobjects.id = syscolumns.id
    inner join sysforeignkeys on syscolumns.id = sysforeignkeys.fkeyid and syscolumns.colid = sysforeignkeys.fkey
    inner join syscolumns syscolumns2 on sysforeignkeys.rkeyid = syscolumns2.id and sysforeignkeys.rkey = syscolumns2.colid
    inner join sysobjects sysobjects2 on syscolumns2.id = sysobjects2.id
    order by sysobjects.name,
    syscolumns.name

    blindman

  6. #6
    Join Date
    Jul 2002
    Posts
    30
    Oh, I like your query better! Thanks, very useful!

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Mine is more direct, but Microsoft might actually recommend your method as the structure of the system tables is subject to change in future versions.

    I'm an under-the-hood database guy, and I don't like referencing views if I don't know what logic they use, and so that's why I go directly to the system tables.

    blindman

  8. #8
    Join Date
    Jul 2002
    Posts
    30
    Yes, they actually do. Microsoft doesn't guarantee that the sys tables won't change and for backwards compatibility, better to use the views; Since they don't change. However, having said that - I still like yours better!

Posting Permissions

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