Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Version Control your Stored Procedures


Version Control your Stored Procedures

Author
Message
mike.renwick-894639
mike.renwick-894639
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 190
I've not come across DDL triggers before- they look quite interesting and a relatively sensible place to capture changes.

I can see these being helpful for evaluating roll-out accuracy. I think my concerns with this approach would probably start to split hairs, off the top of my head, they wouldn't capture anything that lives outside of a database but within the database server (mail profile and accounts, sql server jobs, linked servers for example), or indeed the databases themselves. Moreover, triggers are somewhat simple to disable.

In our setup, we weren't as concerned about being able to undo or redo changes automatically- I guess we're a simpler setup. Also, our VCS integration is significantly more basic than the one you outline here. Mostly we're satisfied if what's in the baseline matches what comes out the source extract.
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1836
mike.renwick-894639 (5/7/2010)
I've not come across DDL triggers before- they look quite interesting and a relatively sensible place to capture changes.


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


mike.renwick-894639
mike.renwick-894639
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 190
Looks like your trigger captures more than just table changes, which is cool - presumably all sorts of alter/create statements include sprocs would find their way into that audit trail.

I like it for active monitoring- obviously the drawback of our setup is that it's a scheduled job and runs daily so all manner of nonsense can happen in between. Might try and implement this on our dev box and take a look at the detail in the xml events generated.

We do like the SMO stuff though, especially using a generic scripter to script a table, index, sql server job, linked server, assembly, without having to really know too much about it. Something quite clean about the powershell approach.

Thanks!
Bill Galashan
Bill Galashan
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 217
Have you thought about the default trace as schema changes are already recorded there and easily available without the need for a ddl trigger. In fact Microsoft provides a schema change history report available as part of the basic installation. Why re-invent the wheel?

Again DDL triggers are part of the toolset and available to be used when most appropriate but a bit overkill to use a DDL trigger to get information on something that is already directly available.

This has also been documented in a good article from 2008

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
yonision
yonision
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 164
For those of you who are willing to pay a bit, there's a product that does just that:

http://www.nobhillsoft.com/Randolph.aspx

install and setup is quick. more than just version control for the entire schema (tables included) it also provides clear reports, search through history, script at any point in time, rollbacks, email aleerts when things change, pushing schema into sourcesafe, subversion, TFS, and more...



Gursoy Yerli
Gursoy Yerli
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
Fixed a bug;

Your generation sproc was ignoring the schema when using "EXEC sp_helptext @ProcedureName". Added below to fix the issue.

DECLARE @abc VARCHAR(MAX)
SET @abc = @RoutineSchema + '.' + @ProcedureName
INSERT INTO @StoredProcsDefinitionTable
(
ProcedureLine
)
EXEC sp_helptext @abc

Good article but It does not apply what we're trying to do.
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1836
Gursoy Yerli (5/11/2010)
Fixed a bug;

Your generation sproc was ignoring the schema when using "EXEC sp_helptext @ProcedureName". Added below to fix the issue.

DECLARE @abc VARCHAR(MAX)
SET @abc = @RoutineSchema + '.' + @ProcedureName
INSERT INTO @StoredProcsDefinitionTable
(
ProcedureLine
)
EXEC sp_helptext @abc

Good article but It does not apply what we're trying to do.

Thanks, I never spotted that all my sprocs are on [dbo] schema.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search