SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblSPHistory](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[SPName] [varchar](500) NULL,
	[HostName] [varchar](50) NULL,
	[SP] [text] NULL,
	[DateCreation] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblSPHistory] ADD  CONSTRAINT [DF_tblSPHistory_DateCreation]  DEFAULT (getdate()) FOR [DateCreation]
GO

/// trigger ---


CREATE TRIGGER [trgSPChangehistory] 
   ON DATABASE 
 FOR 
    create_procedure, alter_procedure, drop_procedure,
 create_table, alter_table, drop_table,
 create_function, alter_function, drop_function,
 create_view, alter_view, drop_view,
 create_trigger, alter_trigger, drop_trigger
AS 
BEGIN
 SET NOCOUNT ON
 begin try
 /*
 http://riteshshah.wordpress.com/2009/03/05/ddl-trigger-in-sql-server-2005-for-create-table-alter-table-drop-table-create-procedure-alter-procedure-drop-procedure-etc/
 */
  DECLARE @xEvent XML
  SET @xEvent = eventdata() --capture eventdata regarding SQL statement user have fired
  declare @objectName  varchar(500)
  set @objectName =  CONVERT(VARCHAR(500), @xEvent.query('data(/EVENT_INSTANCE/ObjectName)'))
  
  INSERT INTO tblSPHistory 
  select @objectName ,
          HOST_NAME() , s.text, getdate() from Syscomments s join sys.objects o on s.id= o.object_ID where name = @objectName

  
  
 end try
 begin catch
 
 end catch 
END
 

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [trgSPChangehistory] ON DATABASE
GO

ENABLE TRIGGER [trgSPChangehistory] ON DATABASE
GO