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
   
https://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];