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.
Go to your database in the Object Explorer and right-click on it. Select Tasks->Generate Scripts (NOT "Script Database as"). In the wizard you can make sure that constraints are scripted and remove other items. Check the generated script and make sure that it does what you need.
Here, we display the name(CONSTRAINT_NAME) and the type of the constraint(CONSTRAINT_TYPE) for all existing constraints. Syntax: SELECT INFORMATION FROM INFORMATION_SCHEMA. TABLE_CONSTRAINTS WHERE TABLE_NAME='TABLE_NAME';
It will not copy constraints. If you want two tables to be set up with the same constraints, you have to do it manually by running the create table/constraint statements. You can have sql server create the sql statements from the existing table though.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With