I've written this trigger to record when DDL changes were made to a table, it's quite useful.

The article I used as the initial source is here: http://www.databasejournal.com/features/mssql/article.php/3685661/Monitoring-Changes-to-your-Database-Schema.htm

If you look at the xml EVENTDATA() there is also the full create statement for the stored procedure under the value /EVENT_INSTANCE/TSQLCommand/CommandText.

-- ************************************************

-- TABLE DDLChangeCatalog

-- DDLChangeCatalog table used by the trigger:

-- ************************************************

DECLARE @ObjectName AS varchar(128)

SET @ObjectName = N'DDLChangeCatalog'

IF NOT EXISTS(SELECT 1

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = @ObjectName)

BEGIN

CREATE TABLE dbo.DDLChangeCatalog

(

EventType nvarchar(100) NOT NULL,

SchemaName nvarchar(100) NOT NULL,

ObjectName nvarchar(100) NOT NULL,

ObjectType nvarchar(100) NOT NULL,

EventDate datetime NULL,

SystemUser nvarchar(100) NULL,

CurrentUser nvarchar(100) NULL,

OriginalUser nvarchar(100) NULL,

CONSTRAINT PK_DDLChangeCatalog PRIMARY KEY CLUSTERED

(

ObjectName ASC,

ObjectType ASC,

EventType ASC,

SchemaName ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

EXEC sys.sp_addextendedproperty @name=N'Summary', @value=N'This table stores DDL changes to the database server' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName

EXEC sys.sp_addextendedproperty @name=N'Used By', @value=N'change monitoring' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'EventType' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'EventType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SchemaName' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'SchemaName'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ObjectName' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'ObjectName'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ObjectType' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'ObjectType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'EventDate' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'EventDate'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SystemUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'SystemUser'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CurrentUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'CurrentUser'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OriginalUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'OriginalUser'

END

GO

IF NOT EXISTS(SELECT *

FROM sys.triggers

WHERE [Name] = 'ChangeDDLCatalog')

BEGIN

EXEC ('CREATE TRIGGER ChangeDDLCatalog ON DATABASE FOR DDL_PROCEDURE_EVENTS AS SELECT 1')

END

GO

-- **********************************************************************

-- This DDL trigger uses the "DDL_DATABASE_LEVEL_EVENTS" event to identify when this trigger should fire.

-- This event will fire whenever any CREATE, ALTER, or DELETE command is executed against any object

-- in the database.

-- This trigger makes use of a series of EVENTDATA() function calls to identify information about

-- which object, and event was used to modify the database. Once all the pertinent data is gathered

-- this trigger then inserted the collected data into the "DDLChangeCatalog" table.

-- $Revision: 1.0 $

-- **********************************************************************

ALTER TRIGGER ChangeDDLCatalog

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

BEGIN

SET NOCOUNT ON

DECLARE @EventType nvarchar(100)

DECLARE @SchemaName nvarchar(100)

DECLARE @ObjectName nvarchar(100)

DECLARE @ObjectType nvarchar(100)

SET ANSI_PADDING ON -- This is needed for when subscriptions are added.

SELECT @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'),

@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'),

@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),

@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')

IF @EventType IS NULL

SET @EventType = N'Unknown'

IF @SchemaName IS NULL

SET @SchemaName = N'Unknown'

IF @ObjectName IS NULL

SET @ObjectName = N'Unknown'

IF @ObjectType IS NULL

SET @ObjectType = N'Unknown'

-- Is the default schema used?

IF @SchemaName = ' '

BEGIN

SELECT @SchemaName = default_schema_name

FROM sys.sysusers U

INNER JOIN sys.database_principals P

ON U.uid = P.principal_id

WHERE U.[Name] = Current_User

END

--END IF

IF @SchemaName IS NULL

BEGIN

SET @SchemaName = 'Unknown'

END

--END IF

IF EXISTS(SELECT 1

FROM dbo.DDLChangeCatalog

WHERE EventType = @EventType

AND SchemaName = @SchemaName

AND ObjectName = @ObjectName

AND ObjectType = @ObjectType)

BEGIN

-- Update existing row

UPDATE dbo.DDLChangeCatalog

SET EventDate = GetDate(),

SystemUser = SUser_SName(),

CurrentUser = Current_User,

OriginalUser = ORIGINAL_LOGIN()

WHERE EventType = @EventType

AND SchemaName = @SchemaName

AND ObjectName = @ObjectName

AND ObjectType = @ObjectType

END

ELSE

BEGIN

-- Update new row

INSERT INTO dbo.DDLChangeCatalog

(

EventType,

SchemaName,

ObjectName,

ObjectType,

EventDate,

SystemUser,

CurrentUser,

OriginalUser

)

VALUES

(

@EventType,

@SchemaName,

@ObjectName,

@ObjectType,

GetDate(),

SUser_SName(),

Current_User,

ORIGINAL_LOGIN()

)

END

--END IF

END

GO