how-to track table DDL changes on sql server 2008

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_MetaTable_IsDeleted]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[MetaTable] DROP CONSTRAINT [DF_MetaTable_IsDeleted]

END

GO

/****** Object:  Table [dbo].[MetaTable]    Script Date: 03/25/2010 15:44:59 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetaTable]') AND type in (N'U'))

DROP TABLE [dbo].[MetaTable]

GO

/****** Object:  Table [dbo].[MetaTable]    Script Date: 03/25/2010 15:44:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[MetaTable](

[MetaTableId] [int] IDENTITY(1,1) NOT NULL,

[DbName] [varchar](256) NOT NULL,

[SchemaName] [varchar](256) NOT NULL,

[Name] [varchar](256) NOT NULL,

[Title] [varchar](200) NULL,

[DocumentationDescription] [varchar](2000) NOT NULL,

[IsPlatform] [bit] NULL,

[IsDeleted] [bit] NULL,

[CreationOrder] [int] NULL,

CONSTRAINT [PK_MetaTable] PRIMARY KEY CLUSTERED 

(

[MetaTableId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MetaTable] ADD  CONSTRAINT [DF_MetaTable_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]

GO

/****** Object:  Table [dbo].[MetaTableHistory]    Script Date: 03/25/2010 15:49:43 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetaTableHistory]') AND type in (N'U'))

DROP TABLE [dbo].[MetaTableHistory]

GO

/****** Object:  Table [dbo].[MetaTableHistory]    Script Date: 03/25/2010 15:49:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[MetaTableHistory](

[MetaTableHistoryId] [int] IDENTITY(1,1) NOT NULL,

[MetaTableId] [int] NOT NULL,

[DbName] [varchar](256) NOT NULL,

[SchemaName] [varchar](256) NOT NULL,

[Name] [varchar](256) NOT NULL,

[Title] [varchar](200) NULL,

[DocumentationDescription] [varchar](2000) NOT NULL,

[IsPlatform] [bit] NULL,

[IsDeleted] [bit] NULL,

[CreationOrder] [int] NULL,

[SqlLogin] [varchar](50) NOT NULL,

[Action] [varchar](50) NOT NULL,

[UpdatedWhen] [datetime] NOT NULL,

CONSTRAINT [PK_MetaTableHistory] PRIMARY KEY CLUSTERED 

(

[MetaTableHistoryId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

--use MyDb ; 

IF  EXISTS (SELECT * FROM sys.triggers 

WHERE parent_class_desc = 'DATABASE' AND name = N'trigDdl_UpdateTableDocumentation')

DISABLE TRIGGER [trigDdl_UpdateTableDocumentation] ON DATABASE

GO

/****** Object:  DdlTrigger [trigDdl_UpdateTableDocumentation]    Script Date: 03/25/2010 12:21:16 ******/

IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'trigDdl_UpdateTableDocumentation')DROP TRIGGER [trigDdl_UpdateTableDocumentation] ON DATABASE

GO

/****** Object:  DdlTrigger [trigDdl_UpdateTableDocumentation]    Script Date: 03/25/2010 12:21:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE trigger [trigDdl_UpdateTableDocumentation]

on database

for CREATE_TABLE , DROP_TABLE

--, alter_TABLE, drop_TABLE

--, create_table, alter_table, drop_table,

--create_function, alter_function, drop_function

as

set nocount on

DECLARE  @data xml

DECLARE  @Event varchar(50)

DECLARE  @tableName varchar(256)

DECLARE  @DbName varchar(200)

DECLARE  @colName varchar (200) 

DECLARE  @DataType varchar(50)

DECLARE  @MaxLength int 

DECLARE  @IsNullable nvarchar(5)

DECLARE  @colMode varchar(10)

DECLARE  @eventType varchar(200)

DECLARE  @loginName varchar(200)

DECLARE  @eventName varchar(200)

DECLARE  @eventDate varchar(200)

DECLARE  @domainName varchar(200)

DECLARE  @SchemaName varchar(200)

DECLARE  @ordinalPosition int 

DECLARE  @colDefault varchar(200)

DECLARE @Documentation varchar(max) 

set @SchemaName = 'dbo'

set @domainName = 'trigMeta_UpdateTableColMetaData'

set @data = EVENTDATA()

set @tableName = cast (@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)') as varchar(256))

set @DbName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')

set @event = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')

set @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')

set @loginName = @data.value ('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') 

set @eventDate = getdate()

set @IsNullable = 'NO'

set @Documentation = convert(nvarchar(max) , @data.query('data(/EVENT_INSTANCE/TSQLCommand[1]/CommandText[1])'))

--REPLACE(string to search, string to find, string to replace)

--debug SELECT '1 DOCUMENTATION IS ' + @documentation

set @Documentation = replace ( convert(nvarchar(max) , @data ) , '&lt;' , '<')

--debug SELECT '2 DOCUMENTATION IS ' + @documentation

set @Documentation = replace ( convert(nvarchar(max) , @Documentation ) , '>' , '>')

--repalce the enters 

set @Documentation = replace ( convert(nvarchar(max) , @Documentation ) , '
' , char(13))

--now get a tmp variable 

declare @xmlData xml 

set @xmlData= convert(xml , @Documentation ) 

--debug SELECT '3 DOCUMENTATION IS ' + @documentation

set @Documentation = convert(nvarchar(max) , @xmldata.query('data(/EVENT_INSTANCE/TSQLCommand[1]/CommandText[1]/doc[1])' ))

DECLARE  @Type varchar(200)

DECLARE  @ObjectId int 

if @event = 'CREATE_TABLE' and @TableName <> 'MetaTable'

begin --if @event = 'CREATE_TABLE'

--WRITE HERE THE INSERT

--DEBUG SELECT 'DOCUMENTATION IS ' + @documentation

DECLARE @TableCount int 

set @TableCount  = ( select count([Name]) from MetaTable where Name=@TableName )

if @TableCount <> 1 

BEGIN 

INSERT INTO [dbo].[MetaTable] ( 

[DbName] ,  --type of varchar

[SchemaName] ,  --type of varchar

[Name],  --type of varchar

[Title],  --type of varchar

[DocumentationDescription]  --type of varchar

) 

VALUES 

( 

@DbName , --type of varchar

'dbo' , --type of varchar todo:change

@TableName , --type of varchar

@TableName , --type of varchar

@Documentation  --type of varchar

)            

end -- the table is not in the MetaTable table if ( @TableCount <> 1 ) 

else 

BEGIN

update MetaTable 

set [DocumentationDescription] = @Documentation

where [Name] = @TableName    

END --

end --if @event = 'CREATE_TABLE'

--if @event = 'ALTER_TABLE'

--begin -- if @event = 'alter_TABLE'

--SELECT  'alter table'

--SELECT  'Documentation -- ' + @Documentation

---- WRITE HERE THE UPDATE

--end -- if @event = 'ALTER_TABLE'

if @event = 'DROP_TABLE' AND @TableName <> 'MetaTable'

begin --if @event = 'DROP_Table'

--SELECT  'drop_table' 

--SELECT  'Documentation -- ' + @Documentation 

UPDATE METATABLE 

SET IsDeleted = convert(bit , 1) , 

[DocumentationDescription] = @Documentation

WHERE [NAME] = @TableName

--        Update [MetaTable] set IsDeleted = 'True' WHERE [ObjectId]=@ObjectId

end --if @event = 'DROP_Table'

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

DISABLE TRIGGER [trigDdl_UpdateTableDocumentation] ON DATABASE

GO

ENABLE TRIGGER [trigDdl_UpdateTableDocumentation] ON DATABASE

GO

/****** Object:  Trigger [trigGen_AfterDeleteOnMetaTable]    Script Date: 03/24/2010 08:03:26 ******/

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trigGen_AfterDeleteOnMetaTable]'))

DROP TRIGGER [dbo].[trigGen_AfterDeleteOnMetaTable]

GO

/****** Object:  Trigger [dbo].[trigGen_AfterDeleteOnMetaTable]    Script Date: 03/24/2010 08:03:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================  

-- Author:  <Yordan Georgiev>  

-- Create date: <Create Date,,>  

-- Description: <Description,,>  

-- =============================================  

CREATE TRIGGER  [dbo].[trigGen_AfterDeleteOnMetaTable]   

ON  [dbo].[MetaTable]  

for Delete  

AS   

BEGIN  

-- SET NOCOUNT ON added to prevent extra result sets from  

-- interfering with SELECT statements.  

SET NOCOUNT ON;  

Insert into MetaTableHistory   

(   

[MetaTableId], --type of int

[DbName], --type of varchar

[SchemaName], --type of varchar

[Name], --type of varchar

[Title], --type of varchar

[DocumentationDescription], --type of varchar

[IsPlatform], --type of bit

[IsDeleted], --type of bit

[CreationOrder] , --type of int

[SqlLogin],  

[Action],  

[UpdatedWhen]  

)  

SELECT   

[MetaTableId], --type of int

[DbName], --type of varchar

[SchemaName], --type of varchar

[Name], --type of varchar

[Title], --type of varchar

[DocumentationDescription], --type of varchar

[IsPlatform], --type of bit

[IsDeleted], --type of bit

[CreationOrder] , --type of int

user_name(),  

'DELETE',  

getdate()

from Deleted  

END  

GO

/****** Object:  Trigger [trigGen_AfterInsertOnMetaTable]    Script Date: 03/24/2010 08:07:27 ******/

IF  EXISTS (SELECT * FROM sys.triggers 

WHERE object_id = OBJECT_ID(N'[dbo].[trigGen_AfterInsertOnMetaTable]'))

DROP TRIGGER [dbo].[trigGen_AfterInsertOnMetaTable]

GO

/****** Object:  Trigger [dbo].[trigGen_AfterInsertOnMetaTable]    Script Date: 03/24/2010 08:07:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================    

-- Author:  <Yordan Georgiev>    

-- Create date: <Create Date,,>    

-- Description: <Description,,>    

-- =============================================    

CREATE TRIGGER  [dbo].[trigGen_AfterInsertOnMetaTable]     

ON  [dbo].[MetaTable]    

for Insert    

AS     

BEGIN    

-- SET NOCOUNT ON added to prevent extra result sets from    

-- interfering with SELECT statements.    

SET NOCOUNT ON;    

Insert into MetaTableHistory     

(     

[MetaTableId], --type of int

[DbName], --type of varchar

[SchemaName], --type of varchar

[Name], --type of varchar

[Title], --type of varchar

[DocumentationDescription], --type of varchar

[IsPlatform], --type of bit

[IsDeleted], --type of bit

[CreationOrder] , --type of int

[SqlLogin],    

[Action],    

[UpdatedWhen]    

)    

SELECT     

[MetaTableId], --type of int

[DbName], --type of varchar

[SchemaName], --type of varchar

[Name], --type of varchar

[Title], --type of varchar

[DocumentationDescription], --type of varchar

[IsPlatform], --type of bit

[IsDeleted], --type of bit

[CreationOrder] , --type of int

user_name(),    

'INSERT',    

getdate()

from MetaTable where MetaTableId in (select MetaTableId from Inserted)  

END    

GO

--exec procUtils_GenerateTableHistorySql 'MetaTable'

/****** Object:  Trigger [trigGen_AfterUpdateOnMetaTable]    Script Date: 11/25/2009 10:35:45 ******/    

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trigGen_AfterUpdateOnMetaTable]'))    

DROP TRIGGER [dbo].[trigGen_AfterUpdateOnMetaTable]    

GO    

/****** Object:  Trigger [dbo].[trigGen_AfterUpdateOnMetaTable]    Script Date: 11/25/2009 10:35:45 ******/    

SET ANSI_NULLS ON    

GO    

SET QUOTED_IDENTIFIER ON    

GO    

-- =============================================    

-- Author:  <Yordan Georgiev>    

-- Create date: <Create Date,,>    

-- Description: <Description,,>    

-- =============================================    

CREATE TRIGGER  [dbo].[trigGen_AfterUpdateOnMetaTable]     

ON  [dbo].[MetaTable]    

for Update    

AS     

BEGIN    

-- SET NOCOUNT ON added to prevent extra result sets from    

-- interfering with SELECT statements.    

SET NOCOUNT ON;    

Insert into MetaTableHistory     

(     

[MetaTableId], --type of int

[DbName], --type of varchar

[SchemaName], --type of varchar

[Name], --type of varchar

[Title], --type of varchar

[DocumentationDescription], --type of varchar

[IsPlatform], --type of bit

[IsDeleted], --type of bit

[CreationOrder] , --type of int

[SqlLogin],    

[Action],    

[UpdatedWhen]    

)    

SELECT     

[MetaTableId], --type of int

[DbName], --type of varchar

[SchemaName], --type of varchar

[Name], --type of varchar

[Title], --type of varchar

[DocumentationDescription], --type of varchar

[IsPlatform], --type of bit

[IsDeleted], --type of bit

[CreationOrder] , --type of int

user_name(),    

'UPDATE',    

getdate()

from MetaTable where MetaTableId in (select MetaTableId from Inserted)    

END    

GO    

--SELECT * FROM [dbo].[MetaTableHistory] ORDER BY 1 DESC 

--SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE '%PROC%'

--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%TABLE%'

--exec sp_HelpText procUtils_GenerateMetaInsert

--exec procUtils_GenerateMetaInsert 'TABLE'

-- FIRST REMOVE THE FOREIGN KEY CONSTRAINTS 

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MetaTableStatus_MetaTable]') AND parent_object_id = OBJECT_ID(N'[dbo].[MetaTableStatus]'))

ALTER TABLE [dbo].[MetaTableStatus] DROP CONSTRAINT [FK_MetaTableStatus_MetaTable]

GO

-- ADD HERE THE DROP TABLE STATEMENTS OF THE REFERENCES TABLES 

truncate table MetaTable 

SET IDENTITY_INSERT [dbo].[MetaTable] ON;

BEGIN TRANSACTION;

INSERT INTO [dbo].[MetaTable]([MetaTableId], [DbName], [SchemaName], [Name], [Title], [DocumentationDescription], [IsPlatform], [IsDeleted], [CreationOrder])

SELECT 1, N'MyDb', N'dbo', N'MetaTable', N'MetaTable', N'The table used to describe all other tables in the database', 1, 0, 1 UNION ALL

SELECT 2, N'MyDb', N'dbo', N'User1', N'User1 table', N'Contains all the user data ', 1, 0, 2 UNION ALL

SELECT 3, N'MyDb', N'dbo', N'NamingConvention', N'NamingConvention',N'Provides basic aggreed naming conventions patterns', 1, 0, 7 

COMMIT;

RAISERROR (N'[dbo].[MetaTable]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

GO

SET IDENTITY_INSERT [dbo].[MetaTable] OFF;

--SELECT TOP 50 * FROM DdlLog ORDER BY 1 DESC

--select 'SELECT ' + convert(varchar(10), row_number() over (ORDER BY create_date )) + ', N''MyDb'', N''dbo'', N''' + name + ''', N''' + name + ''', N''' + name + ' - Description '', 1, 0, 10 UNION ALL '  from sys.tables  

-- AND THE CREATE TABLE STATEMENTS 

-- READDD THE FOREIGN KEYS