Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0324 - SQL Server - Script foreign key creation statements

Feb 24 2014 12:00AM by Nakul Vachhrajani   

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.

Tags: #SQLServer, SQL Server, Administration, DBA, T-SQL, #TSQL, Development, Guidance, HowTo, Community


Nakul Vachhrajani
4 · 36% · 11648
1
 
 
0
Refreshed
 
 
0
Incorrect



Submit

2  Comments  

  • This is just what I'm looking for today! I have a specific use for it, to compare many databases which should contain the same key definitions. I've got the SQL Compare tool, but it seems to compare two databases only. The complexity of this code tells me that I am very wise to look for the work already done!

    I am interested in a comment here http://www.linkedin.com/groups/0324-SQL-Server-Script-foreign-40572.S.5844109414218346500 about this work. I agree with Stavros that this script will be improved by dealing with a constraint which is disabled. In my application, that probably would have been done by mistake, and I want to retrieve a statement that represents the wrong state of the constraint, that it is disabled, so that I can detect it.

    However, I think that Stavros's changes include some simple mistakes (including not qualifying column "is_disabled" with a table alias) and also a fundamental one. I think it's the case that if you create a constraint "WITH NOCHECK" as he proposes, then the constraint is not disabled, although it also is not trusted, because it is not checked for existing data. It is checked for new data.

    Therefore, Stavros's change should also include creating the constraint and then immediately disabling the constraint. I think that this is a necessary step to produce a disabled constraint.

    I think don't need this feature myself, because I will only want to notice and enable a constraint that is disabled, but I think it would be an elegant improvement to the tool, and quite simple to do.

    I will be satisfied to insert '/* DISABLED */' at the start of definition of a foreign key constraint which is disabled!

    A further development could be to have a query with columns for the name of the table to alter, and the name of the constraint, the definition of the constraint itself, its enabled and/or trusted state, and the command to disable or enable it. I haven't worked it all out, but it may be that the statements generated could include the text '/* ENABLED */' in the statement body, to be substituted by the user with whichever combination of WITH CHECK and NOCHECK is needed for their purpose.

    commented on Sep 12 2014 9:28AM
    rjacarnegie
    2915 · 0% · 3
  • Hello!

    Thank-you for the feedback and comments around the associated thread on LinkedIn. Yes this script is a base, a starting point and can be customized as per the requirements.

    commented on Sep 13 2014 3:19PM
    Nakul Vachhrajani
    4 · 36% · 11648

Your Comment


Sign Up or Login to post a comment.

"#0324 - SQL Server - Script foreign key creation statements" rated 5 out of 5 by 1 readers
#0324 - SQL Server - Script foreign key creation statements , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]