Professional Web Applications Themes

SQL DMO + Foreign Keys - Microsoft SQL / MS SQL Server

I have table1 that has a Foriegn Key to table2, using the sql dmo i am able to get the script from Table1 to create the Foreign key with Table2 no problem Set objTable = objDatabase.Tables("table1") strSQLScript = objTable.Script(SQLDMOScript_DRI_ForeignKeys) strSQLScript Now = "ALTER TABLE [TABLE1] ADD CONSTRAINT [FK_TABLE1] FOREIGN KEY ( [Table2Id] ) REFERENCES [TABLE2] ( [Table2Id] ) NOT FOR TION" My question is: Is thier any way to do this backwards, I want to use the DMO and Table2 to find out if any relationships exist. Thus returning TABLE1 and the script as noted above. thanks in advance....

  1. #1

    Default SQL DMO + Foreign Keys

    I have table1 that has a Foriegn Key to table2, using the sql dmo i
    am able to get the script from Table1 to create the Foreign key with
    Table2 no problem

    Set objTable = objDatabase.Tables("table1")
    strSQLScript = objTable.Script(SQLDMOScript_DRI_ForeignKeys)


    strSQLScript Now = "ALTER TABLE [TABLE1] ADD CONSTRAINT [FK_TABLE1]
    FOREIGN KEY ( [Table2Id] ) REFERENCES [TABLE2] ( [Table2Id] ) NOT FOR
    TION"

    My question is: Is thier any way to do this backwards, I want to use
    the DMO and Table2 to
    find out if any relationships exist.

    Thus returning TABLE1 and the script as noted above.

    thanks in advance.
    Steven Campbell Guest

  2. #2

    Default Re: SQL DMO + Foreign Keys

    hi Steven,
    "Steven Campbell" <stevenrcampbellyahoo.ca> ha scritto nel messaggio
    news:e124b691.0307031258.207b7c7dposting.google.c om...
    > I have table1 that has a Foriegn Key to table2, using the sql dmo i
    > am able to get the script from Table1 to create the Foreign key with
    > Table2 no problem
    > My question is: Is thier any way to do this backwards, I want to use
    > the DMO and Table2 to
    > find out if any relationships exist.
    don't know if I understood your point...
    you can use the
    oTable2.enumDependencies (SQLDMODep_Parents) method, which returns a
    QueryResult object where all objects on which the referenced table
    depends... then you hve to dig in the QueryResult to search for the object
    ytpe you are interested with...
    hth
    Andrea Montanari
    [email]montanari_andreavirgilio.it[/email]
    [url]http://www.asql.biz/DbaMgr.shtm[/url]
    DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
    (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
    interface)
    --------- remove DMO to reply


    Andrea Montanari Guest

  3. #3

    Default Re: SQL DMO + Foreign Keys

    Ok, SO I found out that if I am on Table1 I can do a...

    objPreReleaseTable.Keys(3).Script and it will return the proper
    script that will contain the ALTER TABLE ADD CONSTRAINT ....

    The problem I see now is finding out how to know what number(3 for
    this example) .key is associated with the .EnumDependencies that I
    selected.

    So the clear it up a little...

    Set objTable = objDatabase.Tables("Table2")
    Set objResults = objTable.EnumDependencies(SQLDMODep_Children)
    For i = 1 To objResults.Rows
    If objResults.GetColumnString(i, 1) = SQLDMOObj_UserTable Then
    strTable2 = objResults.GetCOlumnString(i,2)
    Set objTable2 = objDatabase.Tables(strTable2)
    'PART I NEED HELP WITH PLEASE..
    strSQLScript = objTable2.Keys(???).Script
    End If
    Next i

    I guess I could Loop the objTable2.Keys and do a compare (maybe
    Instr), determining if "Table2" is in the Script, which actually I
    prolly will do, unless someone else has another suggestion . Thanks

    -src
    Steven Campbell Guest

Similar Threads

  1. Foreign keys tut
    By frizzle in forum MySQL
    Replies: 2
    Last Post: July 18th, 05:45 PM
  2. Sql Import with Foreign Keys
    By ryanmhuc@yahoo.com in forum MySQL
    Replies: 0
    Last Post: May 12th, 08:49 PM
  3. foreign keys
    By Sybrand Bakker in forum Microsoft Access
    Replies: 2
    Last Post: July 25th, 10:04 PM
  4. foreign keys - updated
    By Josh in forum Microsoft Access
    Replies: 2
    Last Post: July 24th, 04:01 PM
  5. DMO + Foreign Keys
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 05:58 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139