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.
If you don't want to buy any tools (which are totally worth their price, BTW), you can always interrogate the system catalog views, and extract the info from there to create scripts you could execute on your new database.
In the case of e.g. the default constraints, this query shows you a list of all the default constraints in your database:
SELECT dc.name 'Constraint Name', OBJECT_NAME(parent_object_id) 'Table Name', c.name 'Column Name', definition FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id ORDER BY OBJECT_NAME(parent_object_id), c.name and based on that, you could of course create a query which would emit T-SQL statements to recreate those default constraints on your target server:
SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(dc.parent_object_id) + '.' + OBJECT_NAME(dc.parent_object_id) + ' ADD CONSTRAINT ' + dc.name + ' DEFAULT(' + definition + ') FOR ' + c.name FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id You'd get something like this (for the AdventureWorks sample DB):
ALTER TABLE dbo.Store ADD CONSTRAINT DF_Store_rowguid DEFAULT((newid())) FOR rowguid ALTER TABLE dbo.Store ADD CONSTRAINT DF_Store_ModifiedDate DEFAULT((getdate())) FOR ModifiedDate ALTER TABLE dbo.ProductPhoto ADD CONSTRAINT DF_ProductPhoto_ModifiedDate DEFAULT((getdate())) FOR ModifiedDate ALTER TABLE dbo.ProductProductPhoto ADD CONSTRAINT DF_ProductProductPhoto_Primary DEFAULT(((0))) FOR Primary ALTER TABLE dbo.ProductProductPhoto ADD CONSTRAINT DF_ProductProductPhoto_ModifiedDate DEFAULT((getdate())) FOR ModifiedDate ALTER TABLE dbo.StoreContact ADD CONSTRAINT DF_StoreContact_rowguid DEFAULT((newid())) FOR rowguid ALTER TABLE dbo.StoreContact ADD CONSTRAINT DF_StoreContact_ModifiedDate DEFAULT((getdate())) FOR ModifiedDate ALTER TABLE dbo.Address ADD CONSTRAINT DF_Address_rowguid DEFAULT((newid())) FOR rowguid Of course, you could tweak the resulting T-SQL being output to your liking - but basically, copy&paste those results from the query to your new database, and off you go.
Of course, there are similar system catalog views for foreign key relationships (sys.foreign_keys), check constraints (sys.check_constraints), indexes (sys.indexes and sys.index_columns) and many more.
It's a bit of work - but it can be done on your own time, and you'll learn a lot about SQL Server in the process.
So it's a traditional "make or buy" decision all over again :-)
Marc
The best way would be to store all your DDL code in a source control. Then deploy it to production using tools like dbGhost (my favorite) or SQL Compare
Red Gate's SQL Compare is a popular, non-free way to do this.
Sure this is an old post, but none of the scripts in all the above answers put out the table schemas as well. So it didn't work out the box for my database.
This one does, so it did:
-- =========================================================== -- Default Constraints -- How to script out Default Constraints in SQL Server 2005+ -- =========================================================== -- view results in text, to make copying and pasting easier -- drop default constraints SELECT 'ALTER TABLE ' + QuoteName(OBJECT_SCHEMA_NAME(sc.id)) + '.' + QUOTENAME(OBJECT_NAME(sc.id)) + CHAR(10) + ' DROP CONSTRAINT ' + QuoteName(OBJECT_NAME(sc.cdefault)) FROM syscolumns sc INNER JOIN sysobjects as so on sc.cdefault = so.id INNER JOIN syscomments as sm on sc.cdefault = sm.id WHERE OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1 -- create default constraints SELECT 'ALTER TABLE ' + QuoteName(OBJECT_SCHEMA_NAME(sc.id)) + '.' + QuoteName(OBJECT_NAME(sc.id)) + ' ADD CONSTRAINT ' + QuoteName(OBJECT_NAME(sc.cdefault))+ ' DEFAULT ' + sm.text + ' FOR ' + QuoteName(sc.name) + CHAR(13)+CHAR(10) FROM syscolumns sc INNER JOIN sysobjects as so on sc.cdefault = so.id INNER JOIN syscomments as sm on sc.cdefault = sm.id WHERE OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1 I adapted it from Donabel Santos's blog here.
EDIT and NB: Be sure to run both parts of the query and save the second result set (i.e. ADD CONSTRAINTs) before dropping your default constraints, else you won't be able to re-create them again (no I didn't do that :)
A good and free Microsoft tool. You can export the schema and the data.
Try DBSourceTools. http://dbsourcetools.codeplex.com
It has schema compare function that will help you create an update script.
Bear in mind though, that you should be using source-code control on your entire database.
This is what DBSourceTools was designed to do - help developers bring their databases under source control.
