Solved

Script to Drop and Recreate All Constraints in a Database

Posted on 2012-03-23
14
1,498 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:Scott Pletcher
ID: 37758457
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
ID: 37758583
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:Scott Pletcher
ID: 37758594
OK.  [Sweet for me as I prefer to script myself anyway :-) ].
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37758800
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:Scott Pletcher
ID: 37758802
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:Scott Pletcher
ID: 37758814
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
ID: 37758910
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37758929
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:
Scott Pletcher earned 500 total points
ID: 37758974
>> 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
ID: 37758993
Excellent solution  delivered in no time! Thanks !
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37758998
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:Scott Pletcher
ID: 37759012
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:Scott Pletcher
ID: 37759014
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:Scott Pletcher
ID: 37759104
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

25 Experts available now in Live!

Get 1:1 Help Now