Powershell Script to Script Create & Drop of Foreign Key Constraints

Not much to more to add after that title.  Use at your own risk.

cls
$server = "ServerName"

$tables = @("Table1", "Table2", "Table3", "Table4", "Table5")
$index_drop_script = "C:\Test\FKDrops.sql"
$index_create_script = "C:\Test\FKCreates.sql"

$database = Get-SqlDatabase -dbname "DatabaseName" -sqlserver $server
$tables = $database.Tables | where{$tables -contains $_.Name}
foreach($table in $tables)
{
	foreach($fk in $table.ForeignKeys)
	{
		#script create FK's
		$scriptingCreateOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
		$scriptingCreateOptions.IncludeDatabaseContext = $false
		$scriptingCreateOptions.IncludeHeaders = $false
		$scriptingCreateOptions.IncludeIfNotExists = $true
		$scriptingCreateOptions.DriForeignKeys = $true
		$fk.Script($scriptingCreateOptions) -join "`nGO`n`n" | Out-File -FilePath $index_create_script -Append
		
		#script drop FK's
		$scriptingDropOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
		$scriptingDropOptions.IncludeIfNotExists = $true
		$scriptingDropOptions.IncludeHeaders = $false
		$scriptingDropOptions.ScriptDrops = $true
		$fk.Script($scriptingDropOptions) -join "`nGO`n`n" | Out-File -FilePath $index_drop_script -Append
		
	}
}


One thought on “Powershell Script to Script Create & Drop of Foreign Key Constraints

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 )

Connecting to %s

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