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();