StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I am too faced with similar dilemma of migrating changes done on development environment to staging and then production. The application(s) I am dealing with demands frequent modification and soon after last role-up it is time for next. If you are in SQL 2005 or above, you are in better place.. Here is what I have come up with. Implement a DDL Trigger on your source database that tracks and log DDL statements into seperate database in the Server instance such as:</p> <pre><code> Create trigger [DDLTRack] ON Database for CREATE_ASSEMBLY ,ALTER_ASSEMBLY ,DROP_ASSEMBLY ,CREATE_ASYMMETRIC_KEY ,ALTER_ASYMMETRIC_KEY ,DROP_ASYMMETRIC_KEY --,ALTER_AUTHORIZATION ,ALTER_AUTHORIZATION_DATABASE ,CREATE_CERTIFICATE ,ALTER_CERTIFICATE ,DROP_CERTIFICATE ,CREATE_CONTRACT ,DROP_CONTRACT --,ADD_COUNTER_SIGNATURE --,DROP_COUNTER_SIGNATURE --,CREATE_CREDENTIAL ,ALTER_CREDENTIAL ,DROP_CREDENTIAL ,GRANT_DATABASE ,DENY_DATABASE ,REVOKE_DATABASE ,CREATE_DEFAULT ,DROP_DEFAULT ,BIND_DEFAULT --,UNBIND_DEFAULT ,CREATE_EVENT_NOTIFICATION ,DROP_EVENT_NOTIFICATION ,CREATE_EXTENDED_PROPERTY --,ALTER_EXTENDED_PROPERTY ,DROP_EXTENDED_PROPERTY ,CREATE_FULLTEXT_CATALOG ,ALTER_FULLTEXT_CATALOG ,DROP_FULLTEXT_CATALOG --,CREATE_FULLTEXT_INDEX ,ALTER_FULLTEXT_INDEX ,DROP_FULLTEXT_INDEX ,CREATE_FUNCTION ,ALTER_FUNCTION ,DROP_FUNCTION --,CREATE_INDEX ,ALTER_INDEX ,DROP_INDEX --,CREATE_MASTER_KEY ,ALTER_MASTER_KEY ,DROP_MASTER_KEY ,CREATE_MESSAGE_TYPE ,ALTER_MESSAGE_TYPE ,DROP_MESSAGE_TYPE ,CREATE_PARTITION_FUNCTION ,ALTER_PARTITION_FUNCTION ,DROP_PARTITION_FUNCTION ,CREATE_PARTITION_SCHEME ,ALTER_PARTITION_SCHEME ,DROP_PARTITION_SCHEME ,CREATE_PLAN_GUIDE ,ALTER_PLAN_GUIDE ,DROP_PLAN_GUIDE ,CREATE_PROCEDURE ,ALTER_PROCEDURE ,DROP_PROCEDURE ,CREATE_QUEUE ,ALTER_QUEUE ,DROP_QUEUE ,CREATE_REMOTE_SERVICE_BINDING ,ALTER_REMOTE_SERVICE_BINDING ,DROP_REMOTE_SERVICE_BINDING --,CREATE_SPATIAL_INDEX -- ,RENAME ,CREATE_ROLE ,ALTER_ROLE ,DROP_ROLE --,ADD_ROLE_MEMBER ,DROP_ROLE_MEMBER ,CREATE_ROUTE ,ALTER_ROUTE ,DROP_ROUTE ,CREATE_RULE ,DROP_RULE ,BIND_RULE ,UNBIND_RULE ,CREATE_SCHEMA ,ALTER_SCHEMA ,DROP_SCHEMA ,CREATE_SERVICE ,ALTER_SERVICE ,DROP_SERVICE --,ALTER_SERVICE_MASTER_KEY --,BACKUP_SERVICE_MASTER_KEY --,RESTORE_SERVICE_MASTER_KEY --,ADD_SIGNATURE ,DROP_SIGNATURE --,CREATE_STATISTICS ,DROP_STATISTICS ,UPDATE_STATISTICS --,CREATE_SYMMETRIC_KEY ,ALTER_SYMMETRIC_KEY ,DROP_SYMMETRIC_KEY ,CREATE_SYNONYM ,DROP_SYNONYM ,CREATE_TABLE ,ALTER_TABLE ,DROP_TABLE ,CREATE_TRIGGER ,ALTER_TRIGGER ,DROP_TRIGGER ,CREATE_TYPE ,DROP_TYPE ,CREATE_USER ,ALTER_USER ,DROP_USER ,CREATE_VIEW ,ALTER_VIEW ,DROP_VIEW ,CREATE_XML_INDEX ,CREATE_XML_SCHEMA_COLLECTION ,ALTER_XML_SCHEMA_COLLECTION ,DROP_XML_SCHEMA_COLLECTION as insert ddlTracking..ddlLog(TSQL,SchemaName,ObjectName,ObjectType,eventXML,LoginName,dbName,EventType) Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') ,EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') ,EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') ,EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') ,EVENTDATA() ,EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') ,EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') ,EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO DISABLE TRIGGER [DDLTRack] ON DATABASE GO ENABLE TRIGGER [DDLTRack] ON DATABASE GO </code></pre> <p>This will i all your changes</p> <p>in (this case) DDlTrack.dbo.DDLlog table.. You can then group last modification done to a database object by sifting the required entries in the Source database and apply them to the Target. HTH.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload