Hello there. I've build database trigger who saves all schema changes in 
some table. and i use it to apply changes in another server.

It works fine as long as i do only shcma changes as part of schema and not 
another actions that relate to schema changes like: sp_rename, (Insert, 
update, delete) on table as part of schema change.

is there a way to add also these changes to the table who save schema 
changes automaticlly? 

Roy Goldhammer (royg@yahoo.com) writes:
> Hello there. I've build database trigger who saves all schema changes in 
> some table. and i use it to apply changes in another server.
> 
> It works fine as long as i do only shcma changes as part of schema and not 
> another actions that relate to schema changes like: sp_rename, (Insert, 
> update, delete) on table as part of schema change.
> 
> is there a way to add also these changes to the table who save schema 
> changes automaticlly? 
 
If you have tables with predefined data, so that INSERT, UPDATE and DELETE
are logically the same as DLL, you could set up triggers on these tables. 
But you would need to find a way to avoid that these triggers are not 
installed on that other server. And since you cannot capture the very 
statement, you would need to store the changes in some other way.

When it comes to sp_rename, this is difficult in SQL 2005. Have a trace 
running to pick up sp_rename is a possibility, but I would not
consider it practical.

In SQL 2008 this is different, as sp_renamed are trapped by DDL triggers. 
(There are a still few system procedures that are not, but they are minor
issues. I think sp_autostats is the most important of these.)

A completely different option is to invest in a tool like SQL Compare
from Red Gate that compares two databases on schema level. They also
have SQL Data Compare to do the same on data.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx