Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Script to Drop and Recreate All Constraints in a Database

Posted on 2012-03-23
14
Medium Priority
?
2,047 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 70

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 70

Expert Comment

by:Scott Pletcher
ID: 37758594
OK.  [Sweet for me as I prefer to script myself anyway :-) ].
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 70

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 70

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 70

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
 
LVL 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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 70

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 70

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 70

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

I have been working with Orchestrator 2012 for a few months now and I must say it has surprised me at every turn, with its functionality and integration capabilities it has really shown me how the System Center Suite of products really all fit toget…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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