CODEWITHSUNDEEP
sql-server
Hardik Shah
Hardik Shah

Reputation: 186

How to track database changes of DDL Statement?

i make changes in database but i have already old database and make changes in new database so how i can identify new database changes.

is anyone having any idea related this using sql server?

Upvotes: 2

Views: 845

Answers (1)

Raj Tamakuwala
Raj Tamakuwala

Reputation: 1163

Nice question hardik

even me too was facing same problem and got good solution.

All you can use is Eventdata() function in sqlserver.it provides all information for particular database changes including sqlcommands in xml format. For more information you can check this link http://msdn.microsoft.com/en-IN/library/ms173781.aspx

here you can do it like this

DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dbo.AdministratorLog(databasename, eventtype,objectname, objecttype,  sqlcommand, loginname)
VALUES(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),  
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)

Upvotes: 2

Related Questions