Can't script the schema for a FK

Question

Post reply

Can't script the schema for a FK

Roust_m

SSCoach

Points: 17410
More actions
April 16, 2013 at 7:26 pm

#394607

Hi,
I use the below script to script out FKs in a database:
#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
Any ideas?
Thanks.
Quick Reply

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nick_UK SSCommitted Points: 1909 More actions · Answer 1

what version of SQL / SQL snapin are you using?

I have tested your script on a SQL2012 instance with the following installed

Name : SQLdmSnapin

PSVersion : 1.0

Description : This is a PowerShell snap-in for accessing SQL diagnostic

manager objects and actions.

Name : SqlServerCmdletSnapin100

PSVersion : 2.0

Description : This is a PowerShell snap-in that includes various SQL Server

cmdlets.

Name : SqlServerProviderSnapin100

PSVersion : 2.0

Description : SQL Server Provider

and it produces what you expect

ALTER TABLE [test].[table2] WITH CHECK ADD CONSTRAINT [FK_MyTable1_MyTable2_ID] FOREIGN KEY([four])

REFERENCES [test].[Table1] ([one])

ON DELETE CASCADE

ALTER TABLE [test].[table2] CHECK CONSTRAINT [FK_MyTable1_MyTable2_ID]

Roust_m SSCoach Points: 17410 More actions · Answer 2

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2),

I run it as part of an SQL Server job.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

I guess I don't understand why don't just read from the system tables/views using TSQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roust_m SSCoach Points: 17410 More actions · Answer 4

Jeff Moden (4/17/2013)
I guess I don't understand why don't just read from the system tables/views using TSQL.

I have to write it to a file later, so it is easier with Powershell. I should be able to run an invoke-sqlcmd to use TSQL with powershell.

What table should I read to get the code for those FKs?