Solved

Script to Drop and Recreate All Constraints in a Database

Posted on 2012-03-23
14
1,458 Views
Last Modified: 2012-03-23
Hi experts:
I need to find a script (MS SQL 2008), that when executed against a database will generate (a second script) all the lines to DROP and RECRETAE ALL constraints (FK, PK) from ALL tables in a database.
Need to do that since I am working on a project where I need to script out all constraints from an existing database (the source DB) on one SQL instance and recreate them in several databases (the destination DBs) on another instance.
The DROP statements are needed because the destination databases might already have some of the constraints created , but not all, so my goal is to create all missing constraints in the destination databases (was thinking of including IF EXISTS in the generated script).
I have been looking online for some solutions but found none so far that will do exactly that. Also, I played a bit with SQL Compare but it generates a lot of addidtionall sripts to modify the destination tables , something I do not want to mess up with.

I am attaching a file that will do the same for all CHECK constraints in a database. Looking for something similar for the PK and FK constraints .
Any help would be appreciated.
Thank you,
RICUser
DROP-and-RECREATE-ALL-CHECK-cons.txt
0
Comment
Question by:RICuser
  • 11
  • 3
14 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If you right-click on the database in SSMS, select "Tasks", you should see an option to "Generate Scripts...".

Select "Specific database objects...", check"Tables", click on "Next", then click on "Advanced".  See if you can adjust the options there to give you what you need.
0
 

Author Comment

by:RICuser
Comment Utility
Yes, I tried using SSMS and it generates a script but this script includes CREATE TABLE statements as well, and at the end of each CREATE TABLE statement  is the creation of the constraints. So, in order to use the SSMS script I need to first drop all destination tables which is not a good solution for me.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
OK.  [Sweet for me as I prefer to script myself anyway :-) ].
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Here's code for FKs:



--Gen script to DROP and CREATE ALL FKs from ALL tables in a database.

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(10, 10),
    sql_line varchar(8000)
    )
   
INSERT INTO #sql
SELECT
    'ALTER TABLE [' + OBJECT_NAME(fkc.parent_object_id) + '] DROP CONSTRAINT ' +
    '[' + OBJECT_NAME(fkc.constraint_object_id) + '] '
FROM sys.foreign_key_columns fkc

INSERT INTO #sql
SELECT 'GO'

INSERT INTO #sql
SELECT
    'ALTER TABLE [' + OBJECT_NAME(fkc.parent_object_id) + '] ADD CONSTRAINT ' +
    '[' + OBJECT_NAME(fkc.constraint_object_id) + '] FOREIGN KEY ( ' +
    (SELECT
         COL_NAME(fkc2.parent_object_id, fkc2.parent_column_id) +
         CASE WHEN fkc2.constraint_column_id < (SELECT MAX(constraint_column_id)
                 FROM sys.foreign_key_columns fkc_par WHERE fkc_par.constraint_object_id = fkc.constraint_object_id)
              THEN ',' ELSE '' END
     FROM sys.foreign_key_columns fkc2
     WHERE
         fkc2.constraint_object_id = fkc.constraint_object_id
     ORDER BY
         fkc2.constraint_column_id
     FOR XML PATH('')
     ) + ') ' +
    'REFERENCES [' + OBJECT_NAME(fkc.referenced_object_id) + '] ( ' +
    (SELECT
         COL_NAME(fkc4.referenced_object_id, fkc4.referenced_column_id) +
         CASE WHEN fkc4.constraint_column_id < (SELECT MAX(constraint_column_id)
                 FROM sys.foreign_key_columns fkc_ref WHERE fkc_ref.constraint_object_id = fkc.constraint_object_id)
              THEN ',' ELSE '' END
     FROM sys.foreign_key_columns fkc4
     WHERE
         fkc4.constraint_object_id = fkc.constraint_object_id
     ORDER BY
         fkc4.constraint_column_id
     FOR XML PATH('')
     ) + '); '
FROM sys.foreign_key_columns fkc
ORDER BY
    OBJECT_NAME(fkc.parent_object_id)
   
SELECT *
FROM #sql
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I deliberately numbered by 10 rather than 1 so it would be easy to add other code, such as "GO" or comments, between existing lines.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Last line of code should be changed; instead of SELECT * FROM #sql it should be:


SELECT sql_line
FROM #sql
ORDER BY id
0
 

Author Comment

by:RICuser
Comment Utility
Thank you Scott!
This is what I have been looking for.
Is there a way we can add a IF EXISTS statement with each ADD CONSTRAINT line so only non-existing constraints can be created in the source DBs and no errors returned?

Thank you for your effort!

RICUser
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Adjusted the FK script:



--Gen script to DROP and CREATE ALL FKs from ALL tables in a database.

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(10, 10),
    sql_line varchar(8000)
    )
   
INSERT INTO #sql
SELECT
    'ALTER TABLE [' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT ' +
    '[' + fk.name + '] '
FROM sys.foreign_keys fk
ORDER BY
    OBJECT_NAME(fk.parent_object_id), fk.name

INSERT INTO #sql
SELECT 'GO'

INSERT INTO #sql
SELECT
    'ALTER TABLE [' + OBJECT_NAME(fk.parent_object_id) + '] ADD CONSTRAINT ' +
    '[' + fk.name + '] FOREIGN KEY ( ' +
    (SELECT
         COL_NAME(fkc2.parent_object_id, fkc2.parent_column_id) +
         CASE WHEN fkc2.constraint_column_id < (SELECT MAX(constraint_column_id)
                 FROM sys.foreign_key_columns fkc_par WHERE fkc_par.constraint_object_id = fk.object_id)
              THEN ', ' ELSE '' END
     FROM sys.foreign_key_columns fkc2
     WHERE
         fkc2.constraint_object_id = fk.object_id
     ORDER BY
         fkc2.constraint_column_id
     FOR XML PATH('')
     ) + ' ) ' +
    'REFERENCES [' + OBJECT_NAME(fk.referenced_object_id) + '] ( ' +
    (SELECT
         COL_NAME(fkc4.referenced_object_id, fkc4.referenced_column_id) +
         CASE WHEN fkc4.constraint_column_id < (SELECT MAX(constraint_column_id)
                 FROM sys.foreign_key_columns fkc_ref WHERE fkc_ref.constraint_object_id = fk.object_id)
              THEN ', ' ELSE '' END
     FROM sys.foreign_key_columns fkc4
     WHERE
         fkc4.constraint_object_id = fk.object_id
     ORDER BY
         fkc4.constraint_column_id
     FOR XML PATH('')
     ) + ' ); '
FROM sys.foreign_keys fk
ORDER BY
    OBJECT_NAME(fk.parent_object_id), fk.name

SELECT sql_line
FROM #sql
ORDER BY id
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
>> Is there a way we can add a IF EXISTS statement with each ADD CONSTRAINT line so only non-existing constraints can be created in the source DBs and no errors returned? <<

Sure.  Keep in mind, though, my code uses the system tables to gen the code, so the constraints must all exist in the db used for generating the code.

Of course the destination db(s) could have none, some or all of the constraints already existing or not.
0
 

Author Closing Comment

by:RICuser
Comment Utility
Excellent solution  delivered in no time! Thanks !
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
With EXISTS / NOT EXISTS checks:



--Gen script to DROP and CREATE ALL FKs from ALL tables in a database.

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(10, 10),
    sql_line varchar(8000)
    )
   
INSERT INTO #sql
SELECT
    'IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = ''' + fk.name + ''')   ' +
    'ALTER TABLE [' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT ' +
    '[' + fk.name + ']; '
FROM sys.foreign_keys fk
ORDER BY
    OBJECT_NAME(fk.parent_object_id), fk.name

INSERT INTO #sql
SELECT 'GO'

INSERT INTO #sql
SELECT
    'IF NOT EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = ''' + fk.name + ''')   ' +
    'ALTER TABLE [' + OBJECT_NAME(fk.parent_object_id) + '] ADD CONSTRAINT ' +
    '[' + fk.name + '] FOREIGN KEY ( ' +
    (SELECT
         COL_NAME(fkc2.parent_object_id, fkc2.parent_column_id) +
         CASE WHEN fkc2.constraint_column_id < (SELECT MAX(constraint_column_id)
                 FROM sys.foreign_key_columns fkc_par WHERE fkc_par.constraint_object_id = fk.object_id)
              THEN ', ' ELSE '' END
     FROM sys.foreign_key_columns fkc2
     WHERE
         fkc2.constraint_object_id = fk.object_id
     ORDER BY
         fkc2.constraint_column_id
     FOR XML PATH('')
     ) + ' ) ' +
    'REFERENCES [' + OBJECT_NAME(fk.referenced_object_id) + '] ( ' +
    (SELECT
         COL_NAME(fkc4.referenced_object_id, fkc4.referenced_column_id) +
         CASE WHEN fkc4.constraint_column_id < (SELECT MAX(constraint_column_id)
                 FROM sys.foreign_key_columns fkc_ref WHERE fkc_ref.constraint_object_id = fk.object_id)
              THEN ', ' ELSE '' END
     FROM sys.foreign_key_columns fkc4
     WHERE
         fkc4.constraint_object_id = fk.object_id
     ORDER BY
         fkc4.constraint_column_id
     FOR XML PATH('')
     ) + ' ); '
FROM sys.foreign_keys fk
ORDER BY
    OBJECT_NAME(fk.parent_object_id), fk.name

SELECT sql_line
FROM #sql
ORDER BY id
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
NOTE that this code depends on the constraint having been given an explicit name, that is, NOT using a constraint name generated by SQL.  Those names have essentially random characters and so can never be matched by name.

If you allow SQL to name your FK constraints ( shame on you :-) ) , then the code would need altered to check for the entire constraint definition -- i.e., parent table, referenced table and all column names would have to be matched to be sure it was the corresponding constraint.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Working on the PK constraint -- it will be very similar, separated it so it could be run stand-alone or with the FK script.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
PRIMARY key script below.  Again, this script relies on constraints being explicitly named.



--Gen script to DROP and CREATE ALL PKs from ALL tables in a database.

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(10, 10),
    sql_line varchar(8000)
    )
   
INSERT INTO #sql
SELECT
    'IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = ''' + i.name + ''' ) ' +
    'ALTER TABLE [' + OBJECT_NAME(i.object_id) + '] ' +
    'DROP CONSTRAINT [' + i.name + ']'
FROM sys.indexes i
WHERE
    i.is_primary_key = 1
ORDER BY
    OBJECT_NAME(i.object_id)

INSERT INTO #sql
SELECT 'GO'

INSERT INTO #sql
SELECT
    'IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name = ''' + i.name + ''' ) ' +
    'ALTER TABLE [' + OBJECT_NAME(i.object_id) + '] ADD CONSTRAINT ' +
    '[' + i.name + '] PRIMARY KEY ( ' +
    (SELECT
         COL_NAME(ic.object_id, ic.column_id) +
         CASE WHEN ic.column_id < (SELECT MAX(column_id)
                 FROM sys.index_columns ic_last WHERE ic_last.object_id = ic.object_id)
              THEN ',' ELSE '' END
     FROM sys.index_columns ic
     WHERE
         ic.object_id = i.object_id
     ORDER BY
         ic.column_id
     FOR XML PATH('')
     ) + '); '
FROM sys.indexes i
WHERE
    i.is_primary_key = 1
ORDER BY
    OBJECT_NAME(i.object_id)

SELECT sql_line
FROM #sql
ORDER BY id
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now