Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    27

    Unanswered: foreign key relationships

    It has been awhile since I have worked with Sybase. Is there a quick way to find all foreign-key relationships for a given database using the system tables? Any insight would be appreciated?

    Patrick Quinn
    Navigant Sybase DBA

  2. #2
    Join Date
    Dec 2003
    Location
    FRANCE (Paris)
    Posts
    23
    Hi,

    Use this simple query :

    SELECT k.id,nm=OBJECT_NAME(k.id),depid,depnm=OBJECT_NAME( depid),k.keycnt,
    joinclause=CONVERT(varchar(255),""), selectkey=CONVERT(varchar(255),""),
    key1,key2,key3,key4,key5,key6,key7,key8,
    depkey1,depkey2,depkey3,depkey4,depkey5,depkey6,de pkey7,depkey8
    INTO #keylist
    FROM syskeys k, sysobjects o
    WHERE depid IS NOT NULL
    AND k.type=2
    AND k.id=o.id
    AND o.type='U'

    UPDATE #keylist
    SET joinclause="p."+COL_NAME(k.id,key1)+"=d."+COL_NAME (k.depid,depkey1),
    selectkey=COL_NAME(k.id, key1)
    FROM #keylist k
    WHERE k.keycnt>=1

    UPDATE #keylist
    SET joinclause=joinclause+" and p."+COL_NAME(k.id,key2)+"=d."+COL_NAME(k.depid,dep key2),
    selectkey=selectkey+", "+COL_NAME(k.id, key2)
    FROM #keylist k
    WHERE k.keycnt>=2

    UPDATE #keylist
    SET joinclause=joinclause+" and p."+COL_NAME(k.id,key3)+"=d."+COL_NAME(k.depid,dep key3),
    selectkey=selectkey+", "+COL_NAME(k.id, key3)
    FROM #keylist k
    WHERE k.keycnt>=3

    UPDATE #keylist
    SET joinclause=joinclause+" and p."+COL_NAME(k.id,key4)+"=d."+COL_NAME(k.depid,dep key4),
    selectkey=selectkey+", "+COL_NAME(k.id, key4)
    FROM #keylist k
    WHERE k.keycnt>=4

    UPDATE #keylist
    SET joinclause=joinclause+" and p."+COL_NAME(k.id,key5)+"=d."+COL_NAME(k.depid,dep key5),
    selectkey=selectkey+", "+COL_NAME(k.id, key5)
    FROM #keylist k
    WHERE k.keycnt>=5

    UPDATE #keylist
    SET joinclause=joinclause+" and p."+COL_NAME(k.id,key6)+"=d."+COL_NAME(k.depid,dep key6),
    selectkey=selectkey+", p."+COL_NAME(k.id, key6)
    FROM #keylist k
    WHERE k.keycnt>=6

    UPDATE #keylist
    SET joinclause=joinclause+" and p."+COL_NAME(k.id,key7)+"=d."+COL_NAME(k.depid,dep key7),
    selectkey=selectkey+", p."+COL_NAME(k.id, key7)
    FROM #keylist k
    WHERE k.keycnt>=7

    UPDATE #keylist
    SET joinclause=joinclause+" and p."+COL_NAME(k.id,key8)+"=d."+COL_NAME(k.depid,dep key8),
    selectkey=selectkey+", p."+COL_NAME(k.id, key8)
    FROM #keylist k
    WHERE k.keycnt>=8


    SELECT nm TableFK,selectkey ColFK,depnm TablePK,joinclause FROM #keylist

    DROP TABLE #keylist
    Mickael

  3. #3
    Join Date
    Sep 2004
    Location
    Stockholm, Sweden
    Posts
    28
    or...
    loop through all tables in sysobjects (they have type = 'U') and execute
    sp_helpconstraint tablename

    just a few rows of code.

    /Mats

Posting Permissions

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