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

cls

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

sl "SQLSERVER:SQL\$InstanceName\DATABASES\$DBName\TABLES"

$tables = gci 

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: