Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script foreign keys Expand / Collapse
Author
Message
Posted Sunday, November 4, 2007 12:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 7:37 AM
Points: 163, Visits: 287
Comments posted to this topic are about the item Script foreign keys
Post #418287
Posted Tuesday, January 8, 2008 2:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 3:00 AM
Points: 251, Visits: 655
See also my script (dec 27 2006, Henk Schreij):
Script for all Foreign Keys of all tables of a DB



Post #439930
Posted Monday, June 9, 2008 9:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:37 AM
Points: 361, Visits: 1,162
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 ¤
Post #513781
Posted Friday, August 13, 2010 7:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 1,326, Visits: 1,282
Hi §unshine,

It works only in SQL Server 2005.

Regards.
Post #968874
Posted Friday, August 13, 2010 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 7:37 AM
Points: 163, Visits: 287
I've just tested it and script works (as expected) in SQL 2008 but not in 2000 as Sunshine tried. Sorry
Post #968915
Posted Wednesday, October 27, 2010 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 7:30 AM
Points: 37, Visits: 220
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.

Post #1011652
Posted Friday, October 29, 2010 2:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 7:37 AM
Points: 163, Visits: 287
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
Post #1012868
Posted Friday, October 29, 2010 3:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 1,020, Visits: 1,290
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
Post #1012885
Posted Friday, October 29, 2010 4:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 7:30 AM
Points: 37, Visits: 220
Yes. I figured it out eventually.
Thanks & Cheers

PII
Post #1012903
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse