Wednesday, July 23, 2014

Changing default constraints with powershell

I don't think it's a secret that I loves me some powershell. I think it's one of those "force multiplier" tools that allow you to do what otherwise might be very tedious fairly simply. Case in point: in this StackOverflow question, the poster asked if there was an easy way to change a user-defined function that was used in multiple default constraints in his database.

If you've ever used a user-defined function for a default constraint and tried to change it, you're well acquainted with the error message that essentially tells you "you can't change the definition of that function because it's being used!". So, in order to change the function, you need to drop all of the default constraints that reference the function, alter the function, and then re-create all of the previously dropped constraints. Depending on how widely used the function is, that could be a lot of work!

The below script will create two T-SQL scripts: one to drop all of the constraints that reference the function and one to add them all back. In practice, you'd run the drop script, alter your function's definition, and then run the create script. As you can see below, I used AdventureWorks2012 for my database and getdate for my function. Change those as appropriate for your situation and you should be good to go.

import-module sqlps;
$s = new-object '.';
$db = $s.databases[ 'adventureworks2012' ];

$drop_options = new-object;
$create_options = new-object;

$drop_options.filename = 'c:\temp\default_drops.sql';
$drop_options.tofileonly = $true;
$drop_options.scriptdrops = $true;
$drop_options.AppendToFile = $true;

$create_options.filename = 'c:\temp\default_creates.sql';
$create_options.tofileonly = $true;
$create_options.scriptdrops = $false;
$create_options.AppendToFile = $true;

foreach ( $table in $db.tables ) {
    foreach ( $column in $table.columns ) {
        if ( $column.defaultconstraint.Text -eq '(getdate())' ) {
            $column.defaultconstraint.script( $drop_options );
            $column.defaultconstraint.script( $create_options );
            $column | select parent, name, defaultConstraint;


No comments:

Post a Comment