SchemaQualifyForeignKeysReferences does not work for SQL Server 2008R2 - by Roust_m

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 788354 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 5/21/2013 8:22:03 PM
Access Restriction Public

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.
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)