Skip to content

June 15, 2012


Powershell Script to Script Create & Drop of Foreign Key Constraints

by Scott Newman

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

$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

Read more from Powershell, SQL Server
1 Comment Post a comment
  1. Aug 25 2012

    Like it Dude Thanks!. The array of table names is a nice thoughtful touch. Touche’


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

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

%d bloggers like this: