Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't script the schema for a FK Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 7:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:08 PM
Points: 1,000, Visits: 1,687
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



Post #1443064
Posted Wednesday, April 17, 2013 9:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 8:18 AM
Points: 1,320, Visits: 1,013
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]
Post #1443347
Posted Wednesday, April 17, 2013 6:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:08 PM
Points: 1,000, Visits: 1,687
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.



Post #1443557
Posted Wednesday, April 17, 2013 7:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 35,859, Visits: 32,532
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443570
Posted Wednesday, April 17, 2013 10:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:08 PM
Points: 1,000, Visits: 1,687
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?



Post #1443592
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse