Setting NOT FOR REPLICATION for all Foreign Key Constraints

Just a quick post on a powershell script I whipped up for setting the NOT FOR REPLICATION on all foreign key constraints in Sql Server. 

Just a word of warning though, this will set your constraints to be not trusted.  If you don’t know what that means, see here.

Unfortunately (AFAIK), the $key.NotForReplication cannot be set and altered for the foreign key object.  It just errors out every time you call the Alter() method on the ForeignKey object.  I tried just setting it and calling the Alter() method on the table instead, but it had no effect.  Instead, I just script it out to the host and run the scripts manually.  Interstingly enough, when you script out the ForeignKey object, the NotForReplication option is indeed included after you set it to $true.  Whatever. 

If one were so enterprising, one could create a function to run the output of the Script() methods directly into the database, but I am not that one.

Usual warning apply, run at your own risk.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$scriptingOptions.ScriptDrops = $true


$InstanceName = "ServerName\InstanceName"  #if not a named instance, ensure you put ServerName\DEFAULT as the instance name
$DBName = "DatabaseName"


$tables = gci 

foreach($table in $tables)
	foreach($key in $table.ForeignKeys)
		$key.NotForReplication = $true
		Write-Host $key.script($scriptingOptions);
		Write-Host $key.Script();
#	$table.Alter();
#	write-host $table.Parent.Name
#	$table.Parent.Alter();

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.