SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Boka / Script foreign keys / Latest Posts

RE: Script foreign keys

Peter Pirker — Fri, 29 Oct 2010 04:44:09 GMT

Yes. I figured it out eventually.Thanks & CheersPII

RE: Script foreign keys

Hardy21 — Fri, 29 Oct 2010 03:47:08 GMT

Thanks for the script.If you want to disable all the constraints of the database then you can simply use: [code="sql"]EXEC sp_MSforeachtable @command1 = 'alter table ? nocheck constraint all'[/code]

RE: Script foreign keys

Nebojsa Ilic — Fri, 29 Oct 2010 02:50:20 GMT

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

RE: Script foreign keys

Peter Pirker — Wed, 27 Oct 2010 09:37:19 GMT

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.

RE: Script foreign keys

Nebojsa Ilic — Fri, 13 Aug 2010 07:35:18 GMT

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

RE: Script foreign keys

SQL_ABD — Fri, 13 Aug 2010 07:00:15 GMT

Hi §unshine,It works only in SQL Server 2005.Regards.

RE: Script foreign keys

sunshine-587009 — Mon, 09 Jun 2008 09:00:29 GMT

This is the error I get when running thisServer: Msg 170, Level 15, State 1, Line 13Line 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 46Must declare the variable '@object_id'.Server: Msg 137, Level 15, State 1, Line 53Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 55Must declare the variable '@DropLine'.Server: Msg 137, Level 15, State 1, Line 56Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 58Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 60Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 61Must declare the variable '@DropLine'.Server: Msg 137, Level 15, State 1, Line 70Must declare the variable '@parent_object_id'.Server: Msg 137, Level 15, State 1, Line 75Must declare the variable '@fkline'.Server: Msg 137, Level 15, State 1, Line 77Must declare the variable '@pkline'.Server: Msg 137, Level 15, State 1, Line 83Must declare the variable '@fkline'.Server: Msg 137, Level 15, State 1, Line 84Must declare the variable '@pkline'.Server: Msg 137, Level 15, State 1, Line 89Must declare the variable '@fkline'.Server: Msg 137, Level 15, State 1, Line 90Must declare the variable '@pkline'.Server: Msg 137, Level 15, State 1, Line 97Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 99Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 100Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 102Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 122Must declare the variable '@DropScript'.Server: Msg 137, Level 15, State 1, Line 123Must declare the variable '@DropLine'.Server: Msg 137, Level 15, State 1, Line 123Must declare the variable '@AddScript'.Server: Msg 137, Level 15, State 1, Line 125Must declare the variable '@AddLine'.Server: Msg 137, Level 15, State 1, Line 125Must declare the variable '@object_id'.Server: Msg 137, Level 15, State 1, Line 135Must declare the variable '@DropScript'.Server: Msg 137, Level 15, State 1, Line 135Must declare the variable '@AddScript'.

RE: Script foreign keys

Henk Schreij — Tue, 08 Jan 2008 02:17:22 GMT

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

Script foreign keys

Nebojsa Ilic — Sun, 04 Nov 2007 00:41:53 GMT

Comments posted to this topic are about the item [B]Script foreign keys[/B]