Script foreign keys

  • Comments posted to this topic are about the item Script foreign keys

  • See also my script (dec 27 2006, Henk Schreij):

    Script for all Foreign Keys of all tables of a DB

  • This is the error I get when running this

    Server: Msg 170, Level 15, State 1, Line 13

    Line 13: Incorrect syntax near 'max'.

    Server: Msg 195, Level 15, State 1, Line 32

    'OBJECT_SCHEMA_NAME' is not a recognized function name.

    Server: Msg 137, Level 15, State 1, Line 46

    Must declare the variable '@object_id'.

    Server: Msg 137, Level 15, State 1, Line 53

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 55

    Must declare the variable '@DropLine'.

    Server: Msg 137, Level 15, State 1, Line 56

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 58

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 60

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 61

    Must declare the variable '@DropLine'.

    Server: Msg 137, Level 15, State 1, Line 70

    Must declare the variable '@parent_object_id'.

    Server: Msg 137, Level 15, State 1, Line 75

    Must declare the variable '@fkline'.

    Server: Msg 137, Level 15, State 1, Line 77

    Must declare the variable '@pkline'.

    Server: Msg 137, Level 15, State 1, Line 83

    Must declare the variable '@fkline'.

    Server: Msg 137, Level 15, State 1, Line 84

    Must declare the variable '@pkline'.

    Server: Msg 137, Level 15, State 1, Line 89

    Must declare the variable '@fkline'.

    Server: Msg 137, Level 15, State 1, Line 90

    Must declare the variable '@pkline'.

    Server: Msg 137, Level 15, State 1, Line 97

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 99

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 100

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 102

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 122

    Must declare the variable '@DropScript'.

    Server: Msg 137, Level 15, State 1, Line 123

    Must declare the variable '@DropLine'.

    Server: Msg 137, Level 15, State 1, Line 123

    Must declare the variable '@AddScript'.

    Server: Msg 137, Level 15, State 1, Line 125

    Must declare the variable '@AddLine'.

    Server: Msg 137, Level 15, State 1, Line 125

    Must declare the variable '@object_id'.

    Server: Msg 137, Level 15, State 1, Line 135

    Must declare the variable '@DropScript'.

    Server: Msg 137, Level 15, State 1, Line 135

    Must declare the variable '@AddScript'.

    ¤ §unshine ¤

  • Hi §unshine,

    It works only in SQL Server 2005.

    Regards.

  • I've just tested it and script works (as expected) in SQL 2008 but not in 2000 as Sunshine tried. Sorry

  • Works great, but...

    I need to use it in a stored proc where it should look up the foreign keys from a twin DB, let's call it DB1 and create them in say, DB2.

    Since switching database context in stored procs is not possible, I prefixed sys.foreign_keys with Db1, and likewise for the column select.

    Tested the select statements, they produced what looked like correct results, but when I ran the entire statement, all I got was nulls.

    Switched context to DB1, and it worked again.

    I've been spinning my wheels on this all afternoon, but can't figure out what goes wrong.

  • Hi Peter,

    If you want this script working with different database then you need to use fully qualified name in the form database.schema.object_name in queries and also to add database id in call of OBJECT_SCHEMA_NAME and OBJECT_NAME functions as second argument (see BOL for full syntax). This caused nulls in your result set (the most probably).

    Regards

  • Thanks for the script.

    If you want to disable all the constraints of the database then you can simply use:

    EXEC sp_MSforeachtable @command1 = 'alter table ? nocheck constraint all'

    Thanks

  • Yes. I figured it out eventually.

    Thanks & Cheers

    PII

  • Thanks for the script.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply