THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

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.

image image

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.

Published Sunday, September 20, 2009 4:52 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Mike said:

Really useful. Thanks.

April 12, 2018 6:07 AM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement