Skip to content

October 12, 2011

Setting NOT FOR REPLICATION for all Foreign Key Constraints

by Scott Newman

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();
Read more from Powershell, SQL Server

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 )

Google+ photo

You are commenting using your Google+ 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

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

%d bloggers like this: