A long time ago, Pinal Dave, a.k.a SQLAuthority (B|T) wrote a post on Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database at the end of which he asked the readers to come up with queries that would re-create the original definition of the primary and foreign keys.

Scripting primary keys is quite easy, but scripting foreign keys is a challenge because of the following:

1. We have both referenced and referencing tables/columns in the mix
2. We may have multiple columns and getting the order of the columns in both – referenced and referencing column lists is important (a column A cannot cross-reference to corresponding column B)
3. We need to (at least everyone should!) specify the update and delete actions

After many years, I got around to this action item and wrote the following script to re-create the original foreign key definition.

USE AdventureWorks2012
GO
;
WITH    ReferentialKeys ( ConstraintName,
                          ReferencedConstraint,
                          DeleteRule,
                          UpdateRule,
                          ReferencingTableSchema,
                          ReferencingTable,
                          ReferencedTableSchema,
                          ReferencedTable )
          AS (
               SELECT   DISTINCT
                        isrc.CONSTRAINT_NAME AS ConstraintName,
                        isrc.UNIQUE_CONSTRAINT_NAME AS ReferencedConstraint,
                        isrc.DELETE_RULE AS DeleteRule,
                        isrc.UPDATE_RULE AS UpdateRule,
                        ReferencingConstraint.TABLE_SCHEMA AS ReferencingTableSchema,
                        ReferencingConstraint.TABLE_NAME AS ReferencingTable,
                        ReferencedConstraint.TABLE_SCHEMA AS ReferencedTableSchema,
                        ReferencedConstraint.TABLE_NAME AS ReferencedTable
               FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS isrc
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencingConstraint
                                    ON isrc.CONSTRAINT_SCHEMA = ReferencingConstraint.CONSTRAINT_SCHEMA
                                   AND isrc.CONSTRAINT_NAME = ReferencingConstraint.CONSTRAINT_NAME
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencedConstraint
                                    ON isrc.UNIQUE_CONSTRAINT_SCHEMA = ReferencedConstraint.CONSTRAINT_SCHEMA
                                   AND isrc.UNIQUE_CONSTRAINT_NAME = ReferencedConstraint.CONSTRAINT_NAME
             ) ,
        ReferencingColumns ( ReferencingConstraint,
                             ReferencedConstraint,
                             ReferencingColumn )
          AS (
               SELECT   ReferentialKeys.ConstraintName AS ReferencingConstraint,
                        ReferentialKeys.ReferencedConstraint AS ReferencedConstraint,
                        CAST((
                               SELECT   ( ','
                                          + QUOTENAME(ReferencingConstraint.COLUMN_NAME) )
                               FROM     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencingConstraint
                               WHERE    ReferencingConstraint.CONSTRAINT_NAME = ReferentialKeys.ConstraintName
                               ORDER BY ReferencingConstraint.ORDINAL_POSITION
                             FOR
                               XML PATH('')
                             ) AS NVARCHAR(MAX)) AS ReferencedColumn
               FROM     ReferentialKeys
             ) ,
        ReferencedColumns ( ReferencingConstraint, ReferencedConstraint, ReferencedColumn )
          AS (
               SELECT   ReferentialKeys.ConstraintName AS ReferencingConstraint,
                        ReferentialKeys.ReferencedConstraint AS ReferencedConstraint,
                        CAST((
                               SELECT   ( ','
                                          + QUOTENAME(ReferencedConstraint.COLUMN_NAME) )
                               FROM     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencedConstraint
                               WHERE    ReferencedConstraint.CONSTRAINT_NAME = ReferentialKeys.ReferencedConstraint
                               ORDER BY ReferencedConstraint.ORDINAL_POSITION
                             FOR
                               XML PATH('')
                             ) AS NVARCHAR(MAX)) AS ReferencedColumn
               FROM     ReferentialKeys
             )
    SELECT  'IF OBJECT_ID(''' + QUOTENAME(ReferentialKeys.ConstraintName)
            + ''',''F'') IS NOT NULL
    BEGIN
        ALTER TABLE ' + QUOTENAME(ReferentialKeys.ReferencingTableSchema)
            + '.' + QUOTENAME(ReferentialKeys.ReferencingTable)
            + '
            WITH CHECK ADD CONSTRAINT '
            + QUOTENAME(ReferentialKeys.ConstraintName) + ' FOREIGN KEY ('
            + SUBSTRING(ReferencingColumns.ReferencingColumn, 2,LEN(ReferencingColumns.ReferencingColumn))
            + ') REFERENCES '
            + QUOTENAME(ReferentialKeys.ReferencedTableSchema) + '.'
            + QUOTENAME(ReferentialKeys.ReferencedTable) + ' ('
            + SUBSTRING(ReferencedColumns.ReferencedColumn, 2,LEN(ReferencedColumns.ReferencedColumn)) + ')'
            + ' ON DELETE ' + ReferentialKeys.DeleteRule + ' ON UPDATE ' + ReferentialKeys.UpdateRule + ';
    END
GO'
    FROM    ReferentialKeys
            INNER JOIN ReferencingColumns
                    ON ReferentialKeys.ConstraintName = ReferencingColumns.ReferencingConstraint
                   AND ReferentialKeys.ReferencedConstraint = ReferencingColumns.ReferencedConstraint
            INNER JOIN ReferencedColumns
                    ON ReferentialKeys.ConstraintName = ReferencedColumns.ReferencingConstraint
                   AND ReferentialKeys.ReferencedConstraint = ReferencedColumns.ReferencedConstraint
    ORDER BY ReferentialKeys.ReferencingTableSchema,
             ReferentialKeys.ReferencingTable,
             ReferentialKeys.ConstraintName
GO

It’s now time to take this query around for a test drive. Here’s the definition of “FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID” that my query produces (formatted for better readability):

IF OBJECT_ID('[FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]','F') IS NOT NULL
    BEGIN
        ALTER TABLE [Sales].[SalesOrderDetail]
            WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
                       FOREIGN KEY ([SpecialOfferID],[ProductID])
                       REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID],[ProductID])
                       ON DELETE NO ACTION
                       ON UPDATE NO ACTION;
    END
GO

And here’s the query that SSMS produces when I ask it to script out the key for me.

IF NOT EXISTS (SELECT *
               FROM sys.foreign_keys
               WHERE object_id = OBJECT_ID(N'[Sales].[FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]')
                 AND parent_object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]'))
    ALTER TABLE [Sales].[SalesOrderDetail]
        WITH CHECK ADD  CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
                   FOREIGN KEY([SpecialOfferID], [ProductID])
                   REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])
GO

Fairly accurate, wouldn’t you say? 🙂

Do share any such useful scripts that you may have in the Scripts page on this site.

Until we meet next time,

Be courteous. Drive responsibly.