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

By:   |   Comments (1)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > PowerShell


I read Aaron Bertrand's tip about dropping and re-creating all foreign key constraints using T-SQL with the FOR XML clause. He invited others to provide other options, so here is my script using PowerShell.


In this tip I'm using the PowerShell module SQLPs as provided with SQL Server 2014.

The single most advantage of scripting using SQLPs (with a tiny bit of SMO visible) is that it will script out all options used with the foreign key constraints (e.g. check/nocheck, on delete action, on update action, replication actions, etc.).

With Foreign Key constraints, it scripts all optional parameters without using a ScriptingOptions attribute when scripting the CREATE DDL.  However, to ensure the generated DDL will get executed in the correct database, I provide ScriptingOption IncludeDatabaseContext. To provide the DELETE DDL, you must provide the appropriate ScriptingOptions attribute ( ScriptDrops = $true ).  Keep in mind other objects you want to script may need their own set of ScriptingOptions to deliver the results you aim for.

PowerShell Script to Drop and Re-create all SQL Server Foreign Keys for a Database

Here is the PowerShell script:

    Generate Drop and re-create FK constraints of a given database

    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 !

    -Date 2014-10-17 - Author Bijnens Johan




write-host $(get-location )

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

    # save original 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


#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.

# 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

Testing the PowerShell Script to Drop and Re-create Foreign Keys

As an example I modified the FK constraints for the table Production.Product in the Adventureworks2012 database, so they use the optional parameters.  As a result the script generated the following.

PowerShell Console Panel content:

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


Content of C:\Users\ALZDBA\AppData\Local\Temp\FKDrops_2014_10_17_203554.sql:

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];

Content of C:\Users\ALZDBA\AppData\Local\Temp\FKCreates_2014_10_17_203554.sql:

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])
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])
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];
Next Steps
  • Test the script and inspect the results in a Development or Test environment before running the actual commands in your production database.
  • Review these other PowerShell related tips.
  • Enjoy the marvels of PowerShell and SQLPs.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Johan Bijnens Johan Bijnens is a database firefighter, working with SQL Server since 2000, working at Aperam Genk.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, April 20, 2022 - 6:36:31 PM - Ashwin Back To Top (90022)
I had implemented this script in my automation, but if a table has a reference key with others we had to capture it

get free sql tips
agree to terms