Using PowerShell to script foreign key creation statements

In my previous post on using T-SQL to script create statements for foreign keys, Chad Miller suggested using PowerShell instead and provided the required script. PowerShell is a SQL Server 2008 feature. I did think about using PowerShell before posting the T-SQL script, but I was trying to provide a solution without any SQL Server 2008 dependencies. But if you're using SQL Server 2008, you can take advantage of PowerShell to really simplify scripting of foreign key creation.

SSMS 2005 on the left, SSMS 2008 on the right showing the new PowerShell feature.

Here’s the resulting PowerShell window:

Notice that the PowerShell window opened with a path matching SSMS Object Explorer. Chad Miller provided this PowerShell script to generate a T-SQL script for creating all foreign keys:

dir | foreach {$_.ForeignKeys} | foreach {$_.Script()} > C:\scripts\createForeignKeys.sql

As you can see, this is a lot easier than the T-SQL code to generate the same results. The key point is that your databases don’t have to be at SQL Server 2008 to take advantage of PowerShell.

Chad also provided a PowerShell script to generate a T-SQL script for dropping all foreign keys:

$scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$scriptingOptions.ScriptDrops = $true
dir | foreach {$_.ForeignKeys} | foreach {$_.Script($scriptingOptions)} > C:\scripts\dropForeignKeys.sql

You can read Chad’s blog here.