Wednesday, October 22, 2014

I'm speaking at SQL Saturday #350 in Winnipeg!

I just got confirmation today that I'll be speaking at SQL Saturday #350 in Winnipeg. If you're up that way, stop in and get some of that fancy learnin'. This will mark my first trip to Canada and now makes me an international speaker. So I've got that going for me, which is nice. :)

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!