THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

image

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

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solution Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement