Get Changes to Objects form DMV's as opposed to Database Triggers

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  November 30, 2011 at 5:13 pm

  #248751

  I had Database triggers for Tables and Stored Procedures.

  However they prevented creating, altering and dropping objects but not all of the time

  I had to disable them because of this.

  This is code for one of the Database Triggers:

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TRIGGER [Backup_Tables]

  ON DATABASE

  FOR CREATE_TABLE, ALTER_TABLE--, DROP_TABLE

  AS

  SET NOCOUNT ON

  DECLARE @Data XML

  SET @Data = EVENTDATA()

  INSERT INTO dbo.changelog(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)')

  )

  GO

  SET ANSI_NULLS OFF

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  I know that you can get this information from a DMV (with the exception of the Login) but I can't remember the DMV Name.

  Does anyone know the name of the DMV or have a SQL Statement that returns this information?

  Any information or ideas would be greatly appreciated?

  Thanks!

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 1, 2011 at 7:25 am

  #1415248

  There's no DMV that stores that info. It's in the default trace, but only until the trace rolls over (5 files of 20MB each)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• GSquared

  SSC Guru

  Points: 260824

  More actions

  December 1, 2011 at 7:35 am

  #1415259

  The default trace, at least for SQL 2008 R2 Dev Edition, does not include the T-SQL command for object creation.

  I just ran this:

  USE ProofOfConcept;

  GO

  CREATE TABLE dbo.DropMe (

  ID INT);

  GO

  SELECT *

  FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log.trc', DEFAULT)

  WHERE DatabaseID = DB_ID('ProofOfConcept');

  The trace includes that a command was issued referencing the object DropMe in that database, but none of the details were in the TextData column, and the BinaryData column is null (of course).

  Are you getting an error message when the trigger blocks execution of object create/modify commands? If so, is it the usual "Incorrect settings regarding ANSI NULLs or QUOTED IDENTIFIERS" type message? That's where I've usually seen errors on DDL triggers. It's because they use XML data columns. You can fix that by setting connection properties correctly before creating objects. That may require changing the default connection properties if you use object-creation/editing tools built into SSMS as opposed to using DDL scripts.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  December 1, 2011 at 9:16 am

  #1415341

  GilaMonster (12/1/2011)

  ---

  There's no DMV that stores that info. It's in the default trace, but only until the trace rolls over (5 files of 20MB each)

  Someone said that I could the same information from the INFORMATION_SCHEMA.ROUTINES as I could from Database Triggers but that is not the case.

  Thanks for your reply.:-)

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/