Script foreign keys

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Boka » Script foreign keys

Script foreign keys

Rate Topic

Display Mode

Topic Options

Author

Message

Nebojsa Ilic

Posted Sunday, November 04, 2007 12:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:59 AM
Points: 163, Visits: 283

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

Post #418287

Henk Schreij

Posted Tuesday, January 08, 2008 2:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:02 AM
Points: 235, Visits: 560

See also my script (dec 27 2006, Henk Schreij):
Script for all Foreign Keys of all tables of a DB

Post #439930

sunshine-587009

Posted Monday, June 09, 2008 9:00 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 PM
Points: 360, Visits: 1,072

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

SQL_ABD

Posted Friday, August 13, 2010 7:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:29 PM
Points: 1,326, Visits: 1,265

Hi §unshine,

It works only in SQL Server 2005.

Regards.

Post #968874

Nebojsa Ilic

Posted Friday, August 13, 2010 7:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:59 AM
Points: 163, Visits: 283

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

Post #968915

Peter Pirker

Posted Wednesday, October 27, 2010 9:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:28 AM
Points: 36, Visits: 206

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

Nebojsa Ilic

Posted Friday, October 29, 2010 2:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:59 AM
Points: 163, Visits: 283

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

Hardy21

Posted Friday, October 29, 2010 3:47 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:42 PM
Points: 877, Visits: 1,158

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

Peter Pirker

Posted Friday, October 29, 2010 4:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:28 AM
Points: 36, Visits: 206

Yes. I figured it out eventually.
Thanks & Cheers

PII

Post #1012903

« Prev Topic | Next Topic »

Permissions