Drop and Re-Create All Foreign Key Constraints in a SQL Server database using PowerShell

<#
.SYNOPSIS
    Generate Drop and re-create FK constraints of a given database

.DESCRIPTION
    Generate Drop and re-create FK constraints of a given database.
    The PowerShell version for Aaron Bertrand's T-SQL version ( see Link )

    Because this is using SQLPs objects, I'm sure all options will get scripted out ! (unless a bug in SQLPs/SMO)

    Keep in mind I'm using SQLPS 2014 !

.NOTES
    -Date 2014-10-17 - Author Bijnens Johan

.LINK
    http://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

#>


clear-host

write-host $(get-location )

# Check module SQLPS
if ( !(get-module -name SQLPs ) ) {

    # save original location
    Push-Location
    # SQLPs will set the current location to SQLSERVER:\ !!
    # -DisableNameChecking -> avoid remarks about non-discoverable function names
    import-module -name SQLPs -DisableNameChecking | out-null

    #reset current location to original location
    Pop-Location

    }

#Interrupt when errors occur
Trap {
    # Handle the error
    $err = $_.Exception
    write-host $err.Message
    while( $err.InnerException ) {
        $err = $err.InnerException
        write-host $err.Message
        };
    # End the script.
    break
    }

# Using Windows authenticated connection
$db = Get-SqlDatabase -ServerInstance "YourServer\YourInstance" -Name 'YourDatabase' ;

# To ensure the statements are generated for execution in the correct database -> IncludeDatabaseContext = $true
$CreateScriptingOptions = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$CreateScriptingOptions.IncludeDatabaseContext = $true

$DropScriptingOptions = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$DropScriptingOptions.IncludeDatabaseContext = $true
$DropScriptingOptions.ScriptDrops = $true

#initialize result arrays
$Drops = @()
$Creates = @()

#Process all tables
foreach ( $Tb in $db.Tables ) {
    #Process all FK for Tb
    foreach ( $Fk in $Tb.ForeignKeys ) {
        # Because I'm using IncludeDatabaseContext = $true, I have to -join the results
        # or object translation will not result in regular text

        # just as a precaution first script out the Create DDL
        #Script Create DDL and add a semicolon
        $Creates += $('{0};' -f ( -join $( $Fk.Script( $CreateScriptingOptions ) -join '; ').tostring() ))
        #Script Drop
        $Drops += $('{0};' -f $( -join $( $Fk.Script( $DropScriptingOptions ) -join ';' ).tostring() ) )
        }
    }

#Export Results
$Stamp = Get-Date -Format "yyyy_MM_dd_HHmmss" ;
$FileDropDDL = $('{0}\FKDrops_{1}.sql' -f $env:temp, $Stamp ) ;
$FileCreateDDL = $('{0}\FKCreates_{1}.sql' -f $env:temp, $Stamp ) ;

Write-host 'Exporting results to :'
Write-host ' Drop DDL :' $FileDropDDL ;
Write-host ' Create DDL :' $FileCreateDDL ;


$Drops | Out-File -FilePath $FileDropDDL -NoClobber -Encoding default ;

$Creates | Out-File -FilePath $FileCreateDDL -NoClobber -Encoding default ;

# Launch the files with their default application
& "$FileDropDDL";
& "$FileCreateDDL"

Write-host 'Script Ended' -BackgroundColor Yellow -ForegroundColor Black

C:\Users\ALZDBA
Exporting results to :
 Drop DDL : C:\Users\ALZDBA\AppData\Local\Temp\FKDrops_2014_10_17_203554.sql
 Create DDL : C:\Users\ALZDBA\AppData\Local\Temp\FKCreates_2014_10_17_203554.sql
Script Ended

PS C:\Users\ALZDBA>

USE [Adventureworks2012];
ALTER TABLE [Production].[Product] DROP CONSTRAINT [FK_Product_ProductModel_ProductModelID];

USE [Adventureworks2012];
ALTER TABLE [Production].[Product] DROP CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID];

USE [Adventureworks2012];
ALTER TABLE [Production].[Product] DROP CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode];

USE [Adventureworks2012];
ALTER TABLE [Production].[Product] DROP CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode];

USE [Adventureworks2012];
ALTER TABLE [Production].[ProductSubcategory] DROP CONSTRAINT [FK_ProductSubcategory_ProductCategory_ProductCategoryID];

USE [Adventureworks2012];
ALTER TABLE [Production].[Product] WITH NOCHECK ADD CONSTRAINT [FK_Product_ProductModel_ProductModelID] FOREIGN KEY([ProductModelID]) REFERENCES [Production].[ProductModel] ([ProductModelID]) NOT FOR REPLICATION;
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_ProductModel_ProductModelID];

USE [Adventureworks2012];
ALTER TABLE [Production].[Product] WITH CHECK ADD CONSTRAINT FK_Product_ProductSubcategory_ProductSubcategoryID] FOREIGN KEY([ProductSubcategoryID])
REFERENCES [Production].[ProductSubcategory] ([ProductSubcategoryID])
ON DELETE CASCADE;
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID];

USE [Adventureworks2012];
ALTER TABLE [Production].[Product] WITH NOCHECK ADD CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode] FOREIGN KEY([SizeUnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ON UPDATE CASCADE
ON DELETE CASCADE
NOT FOR REPLICATION;
ALTER TABLE [Production].[Product] NOCHECK CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode];

USE [Adventureworks2012];
ALTER TABLE [Production].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode] FOREIGN KEY([WeightUnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode]);
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode];

USE [Adventureworks2012];
ALTER TABLE [Production].[ProductSubcategory] WITH CHECK ADD CONSTRAINT [FK_ProductSubcategory_ProductCategory_ProductCategoryID] FOREIGN KEY([ProductCategoryID])
REFERENCES [Production].[ProductCategory] ([ProductCategoryID]);
ALTER TABLE [Production].[ProductSubcategory] CHECK CONSTRAINT [FK_ProductSubcategory_ProductCategory_ProductCategoryID];