.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

T-SQL Scripts to copy all table constraints

Posted By: Shravya     Posted Date: October 05, 2009    Points:2   Category :Sql Server
Hi,
I have created many tables on my local database and moved them to production database.

Now I am working on fine tuning the database and created many constraints on my local database tables such as PK, FK, Default Values, Indexes etc. etc.

Now I would like to copy only these constraints to production database. Is there a way to do it?

Please note that my production database tables already populated with some data. So I can't drop and recreate them.

Thank you & Regards.
Shravya.

Responses
Author: Amit Mehra             
Posted Date: October 05, 2009     Points: 5   

Hi,

Well if already have some data in the production server environment than do the following

1) If you are making changes to stored procedure and views, just right click on them on the development server and select "script stored procedure as" alter, then run that code against production.

2) Verify the generated script.

3) If you are doing changes in Table structure than take the alter script..make sure..drop table statement should not be present in the script

eg
-- to change the table structure
ALTER TABLE dbo.TableA ADD column12 varchar(200)

--To add the constraint
ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)

4) Check again your whole script for drop keyword carefully... once you are sure..than run this script in the production environment.

Hope this will help you.

Thanks
Amit

Thanks,
Amit
Please "Mark As Answer " if this post help you.
Author: Shravya             
Posted Date: October 05, 2009     Points: 5   

Hi Amit,

Thank you very much for the reply.

eg
-- to change the table structure
ALTER TABLE dbo.TableA ADD column12 varchar(200)

--To add the constraint
ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)


I can do like this but the problem is that I have many tables and added many constraints to them. So do i need to go to individual table and get the these constraints and copy to notepad and run it on production database.


Is there any t-sql which gets all these constraints at a shot into a sql file?


Best Regards...

Author: Amit Mehra             
Posted Date: October 05, 2009     Points: 5   

Hi,

You have to each table manually..but lets say you have 3 constraints in a table you can have one alter statement with 3 add contraint keyword for all the changes in 1 table, so there will be one alter statement correspond to each table...

i know this will be a pain..but that is also called SQLHell

Thanks
Amit

Thanks,
Amit
Please "Mark As Answer " if this post help you.


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend