mssqltips logo

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

By:   |   Updated: 2015-01-02   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > PowerShell

Problem

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.

Solution

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:

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

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:

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>

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


Last Updated: 2015-01-02


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools