How to get SQL Server schema changes especially “column name” changes

up vote0down votefavorite 1

I am using trigger to trace DDL changes but when I change column name from the SQL Server Management Studio the trigger is not working? create TRIGGER trgLogDDLEvent ON DATABASE   FOR DDL_DATABASE_LEVEL_EVENTSFOR AS DECLARE @data XMLSET @data = EVENTDATA() IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_STATISTICS' INSERT INTO db_trc_DDLChangeLog( EventType, ObjectName, ObjectType, tsql, RecDate, HostName) VALUES ( @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), getdate(),  HOST_NAME() ) ; GO --select * from db_trc_DDLChangeLog --create table db_trc_DDLChangeLog --( --EventType varchar(max), --ObjectName varchar(max), --ObjectType varchar(max), --tsql varchar(max), --RecDate datetime, --HostName varchar(max) --) sql sql-server sql-server-2005 tsql link|edit|delete|flag edited Oct 22 at 13:21 asked Oct 22 at 12:54 MahmutHAKTAN 325 75% accept rate

If you are changing database structure from the Object Explorer and not a script, then stop that right now. No database change should ever be made without a script that is in source control. – HLGEM Oct 22 at 13:17 I just want to trace this changes for security. – MahmutHAKTAN Oct 22 at 13:28  add comment

1 Answer

activenewestvotes

up vote0down voteaccept

You won't get events specifically for column changes. Instead you'll receive an ALTER_TABLE event to indicate the table has changed. When you say SQL Server console do you mean Management Studio? If so, have a look at the SQL it is generating to alter your table. It might be creating a new table, copying the data to it, dropping the old table and renaming the new table so you will see a number of events like CREATE_TABLE and thenDROP_TABLE. link|flag edited Oct 22 at 14:00 answered Oct 22 at 13:16 Andrew Kennan 2,788212

Yes I mean Management studio. trigger is running for all DLL events is't it?: FOR DDL_DATABASE_LEVEL_EVENTS. I also tried CREATE_TABLE, ALTER_TABLE, DROP_TABLE, events and is was not worked. – MahmutHAKTAN Oct 22 at 13:22    up vote   flag Is your trigger being called at all? Could there be some other problem preventing a row being inserted into db_trc_DDLChangeLog? Also, if the SQL generated by management studio is using sp_rename it won't cause a DDL event to be raised in SQL Server 2005. – Andrew Kennan Oct 22 at 13:59 no problem for db_trc_DDLChangeLog because when i drop table it writes to log ı think sp_rename not firing DDL event.. – MahmutHAKTAN Oct 25 at 13:01  Yeah - sp_rename was fixed for SQL 2008 but in 2005 it won't raise an event. – Andrew Kennan Oct 26 at