Home Dashboard Directory Help
Search

SchemaQualifyForeignKeysReferences does not work for SQL Server 2008R2 by Roust_m


Status: 

Active


2
0
Sign in
to vote
Type: Bug
ID: 788354
Opened: 5/21/2013 8:22:03 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description


Hi,

I use the below script to script out FKs in a database on SQL Server 2008R2 SP1/SP2:
(it works perfectly on SQL Server 2012)

#DECLARE TIMESTAMP FOR THE FILES
$timestamp = Get-Date -Format yyyy-MM-dd
#SCRIPT
SL SQLSERVER:\SQL\'MyServer'\DEFAULT\Databases\'MyDB'\Tables
$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.IncludeIfNotExists = 1
$so.SchemaQualify = 1
$so.SchemaQualifyForeignKeysReferences = 1
$so.ScriptSchema = 1
dir | foreach {$_.ForeignKeys} | foreach {$_.Script()} > "Z:\MyDB\03_FKs $timestamp .sql"

The result I get looks like this:

ALTER TABLE [MySchema].[MyTable1] WITH CHECK ADD CONSTRAINT [FK_MyTable1_MyTable2_ID] FOREIGN KEY([ID])
REFERENCES [MyTable2] ([ID])
ON DELETE CASCADE

I am getting an error when using the above script:

Msg 1767, Level 16, State 0, Line 1

Foreign key 'FK_MyTable1_MyTable2_ID' references invalid table 'MyTable2'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.



The reason for this is because MyTable2 belongs to a schema other then 'dbo', so the generated script should look like this:

ALTER TABLE [MySchema].[MyTable1] WITH CHECK ADD CONSTRAINT [FK_MyTable1_MyTable2_ID] FOREIGN KEY([ID])
REFERENCES [MySchema].[MyTable2] ([ID])
ON DELETE CASCADE

Please see this post on SQLServerCentral.com, it is a bit easier to read and has other peoples replys:
http://www.sqlservercentral.com/Forums/Topic1443064-1351-1.aspx#bm1443592

Thanks.
Details
Sign in to post a comment.
Posted by Microsoft on 5/22/2013 at 4:36 PM
Hello Roust. Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you. -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Sign in to post a workaround.