Friday, October 17, 2014

Script foreign key drops and creates: invitation accepted!

This morning as I was going through my morning reading, I came across a post that I knew would be relevant to my interests. Specifically, Aaron Bertrand wrote about a script that he had written to drop and re-create all the foreign keys in a given database. And this time, he explicitly put out an invitation for powershell solutions to the same problem (so I don't feel too bad about it this time). So here I am and here we go.
import-module sqlps -disablenamechecking

$drop_filename = 'c:\temp\fk_drop.sql';
$create_filename = 'c:\temp\fk_create.sql';

if (test-path $drop_filename) {
    remove-item $drop_filename;
}

if (test-path $create_filename) {
    remove-item $create_filename;
}

$drop_options = new-object microsoft.sqlserver.management.smo.scriptingoptions
$drop_options.ToFileOnly = $true;
$drop_options.FileName = $drop_filename;
$drop_options.AppendToFile = $true;

$drop_options.ScriptDrops = $true;
$create_options = new-object microsoft.sqlserver.management.smo.scriptingoptions
$create_options.ToFileOnly = $true;
$create_options.FileName = $create_filename;
$create_options.AppendToFile = $true;

$s = new-object microsoft.sqlserver.management.smo.server '.';
$db = $s.databases['AdventureWorks2014'];

foreach ($table in $db.Tables) {
    foreach ($fk in $table.ForeignKeys) {
        $fk.script( $create_options );
        $fk.script( $drop_options );
    }
}
As you can see, most of the code is preparations (deleting the files if they're already there and setting up the ScriptingOptions objects). The actual meat of it is just a couple of lines long. If you run this script, you'll end up with two T-SQL files that you can run: one to drop the foreign keys and one to re-create the foreign keys. Enjoy!

No comments:

Post a Comment