Tracking Schema Change Details in SQL Server

In SQL Server 2005 and newer you can create database catalog wide triggers that you can use to track all schema changes to any objects in that database.

I wanted to track all schema changes to all catalogs on my server, so what I did was to create a database catalog called SchemaChangeLog that contained a table and a SP as follows:

USE [SchemaChanges]
GO

/****** Object:  Table [dbo].[SchemaChangeLog]    Script Date: 01/27/2011 11:17:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SchemaChangeLog](
    [SchemaChangeLogId] [int] IDENTITY(1,1) NOT NULL,
    [EventType] [varchar](100) NULL,
    [PostTime] [datetime] NULL,
    [LoginName] [varchar](100) NULL,
    [Username] [varchar](100) NULL,
    [DatabaseName] [varchar](100) NULL,
    [SchemaName] [varchar](100) NULL,
    [ObjectName] [nchar](100) NULL,
    [ObjectType] [varchar](100) NULL,
    [SqlText] [varchar](max) NULL,
    [ServerName] [varchar](100) NULL,
 CONSTRAINT [PK_SchemaChangeLog] PRIMARY KEY CLUSTERED
(
    [SchemaChangeLogId] 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

SET ANSI_PADDING OFF
GO


USE [SchemaChanges]
GO

/****** Object:  StoredProcedure [dbo].[SchemaChangeLog_Save]    Script Date: 01/27/2011 11:17:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Chris May
-- Create date: 1/27/11
-- Description:    saves schema change info
-- =============================================
CREATE PROCEDURE [dbo].[SchemaChangeLog_Save]
      @data XML
AS
BEGIN
    BEGIN TRY
        
        insert  INTO schemachanges.[DBO].SchemaChangeLog
                (
                  [EventType],
                  PostTime,
                  ServerName,
                  LoginName,
                  Username,
                  DatabaseName,
                  SchemaName,
                  ObjectName,
                  ObjectType,
                  SqlText

                )
        VALUES  (
                  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),
                  GETDATE(),
                  @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),
                  @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
                  @data.value('(/EVENT_INSTANCE/UserName)[1]', 'sysname'),
                  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname'),
                  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'sysname'),
                  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(max)')
                ) ;

    END TRY
    BEGIN CATCH
        -- we just don't want any errors here
                
    END CATCH ;
END

GO

Then on each database catalog you want to track schema changes on, just run this to create the trigger:

create TRIGGER Trigger_Track_Schema_Changes ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
AS
    SET NOCOUNT ON
    DECLARE @data XML ;
    BEGIN TRY

        SET @data = EVENTDATA() ;
        EXEC SchemaChanges.dbo.SchemaChangeLog_Save @data

    END TRY
    BEGIN CATCH
            -- we just don't want any errors here
    END CATCH ;

go

If you want to undo this you can always drop the trigger by using:

drop TRIGGER Trigger_Track_Schema_Changes ON DATABASE

EDIT: I’ve found that some things (like some actions in SQL Compare) cause this trigger to throw an error.  I’m not sure why.  But, if you want to just disable the trigger for a moment you can run this:

disable trigger Trigger_Track_Schema_Changes on database

 

Advertisements

Converting video clips between types

I had a small WMV file that I wanted to convert into a stand alone flash video (not a FLV, a SWF) to stick on a website.

There are lots of seemingly poor options out there for doing this, but this one was perfect: http://www.youconvertit.com.

I uploaded the WMV and they had a number of output options, one of which was SWF.  They emailed me a few seconds later letting me know my conversion job was completed, and I downloaded the SWF, which worked just as expected.

 

Ignore White Space in Visual Studio built in compare tool

If you don’t really care about spaces vs tabs when comparing files in visual studio using the built in compare tool you can use the info found here to get VS to ignore white space.

Tools -> Options -> Source Control -> Visual Studio Team
Foundation Server -> Configure Users Tools.. -> Add ->

Add “%5 /ignorespace” at the end of the “arguments” textbox.

That’s it.