February 4, 2009 at 7:31 am
I need create a DDL Trigger to control DROP objects. I know it's easy. But before the object is dropped, I want to capture the code for object. Ex: When I DROP a Procedure, I want to capture its code and insert this code into audit table.
February 4, 2009 at 10:30 pm
I took one of the examples I'd gotten here and modified it to get teh sp_helptext of the procedure being affected.
my audit trigger is called AFTER the CREATE/ALTER DROP event...so it works fine for capturing the proc definition for create and alter, but not for DROP.
the procedure is dropped BEFORE my audit trigger can get the sp_helptext. i get an error saying object
Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'sp_find' does not exist in database 'GEO55' or is invalid for this operation.
maybe there is a different audit event, like BEFORE DROP_PROCEDURE, i'm not sure, and it's late
here's my example code...after putting it in place in a database, create/alter then drop a procedure, then select * from DDLEventLog to see the results
[font="Courier New"]
CREATE TABLE [dbo].[DDLEventLog](
[EventDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[objectName] [sysname] NOT NULL,
[CommandText] [varchar](MAX) NOT NULL,
[EventType] [nvarchar](100) NULL
)
--
GO
CREATE TRIGGER [ReturnPREventData]
ON DATABASE
FOR
CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE
AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME,
@OriginalProcDef VARCHAR(MAX)
DECLARE @tmp TABLE (objectDef VARCHAR(MAX) )
SET @eventData = eventdata()
SET @OriginalProcDef=''
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
--get the definition of the proc as it stands.
INSERT INTO @tmp(objectDef)
EXEC sp_helptext @oname
SELECT @OriginalProcDef = @OriginalProcDef + ISNULL(objectDef,'')
FROM @tmp
IF @OriginalProcDef <> ''
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES
(@edate,@uname,@oname,@OriginalProcDef,'Save of Original Proc Definition')
END
IF @uname IS NOT NULL
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES
(@edate,@uname,@oname,@otext,@etype)
END
GO
ENABLE TRIGGER [ReturnPREventData] ON DATABASE
GO
SELECT * FROM [DDLEventLog][/font]
Lowell
February 4, 2009 at 10:37 pm
From BOL:
DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.
So I think if you simply do CREATE and ALTER, you will always have a copy of a proc before it gets dropped.
hope that helps.
http://msdn.microsoft.com/en-us/library/ms175941.aspx
Lowell
February 5, 2009 at 5:18 am
this updated code that i posted previously does not raise any errors. it cannot get teh definition on drop procedure, but it does get creation and changes.
[font="Courier New"]
CREATE TABLE [dbo].[DDLEventLog](
[EventDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[objectName] [sysname] NOT NULL,
[CommandText] [varchar](MAX) NOT NULL,
[EventType] [nvarchar](100) NULL
)
--
GO
ALTER TRIGGER [ReturnPREventData]
ON DATABASE
FOR
CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE
AS
SET NOCOUNT ON
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME,
@OriginalProcDef VARCHAR(MAX)
DECLARE @tmp TABLE (objectDef VARCHAR(MAX) )
SET @eventData = eventdata()
SET @OriginalProcDef=''
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
--get the definition of the proc as it stands.
IF @etype = 'CREATE_PROCEDURE' OR @etype = 'ALTER_PROCEDURE'
BEGIN
INSERT INTO @tmp(objectDef)
EXEC sp_helptext @oname
END
SELECT @OriginalProcDef = @OriginalProcDef + ISNULL(objectDef,'')
FROM @tmp
IF @OriginalProcDef <> ''
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType)
VALUES (@edate,@uname,@oname,@OriginalProcDef,'Save of Original Proc Definition')
END
--regular audit continues.
IF @uname IS NOT NULL
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES
(@edate,@uname,@oname,@otext,@etype)
END
GO
ENABLE TRIGGER [ReturnPREventData] ON DATABASE
GO
SELECT * FROM [DDLEventLog][/font]
Lowell
February 6, 2009 at 4:49 pm
Note that even if the table were still around, it would still be extremely difficult to get exactly what the OP is asking for. This is because SQL Server has no built-in way to generate SQL CREATE scripts for its non-script based objects. Yes, SSMS & SMO can do it, but they are outside of SQL Server, and there is no supported way to call them for that purpose. And although most static objects are simple enough that a small sProc could recreate the script from the object attributes, the one enormous exception to that is Tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 6, 2009 at 5:46 pm
i've got a stored proc i made that i named sp_getDDL, which trolls thru the table definition and it's columns and builds the script for the table; it's probably worthy of an article. I wrote it for sql2000 way back, and adapted it for 2005 as well.
Lowell
February 6, 2009 at 7:18 pm
Lowell (2/6/2009)
i've got a stored proc i made that i named sp_getDDL, which trolls thru the table definition and it's columns and builds the script for the table; it's probably worthy of an article. I wrote it for sql2000 way back, and adapted it for 2005 as well.
Or at least post it as a script. Writing a fairly complete "CREATE TABLE" proc is a herculean task, so I would love to have a copy.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 7, 2009 at 3:31 am
RBarryYoung (2/6/2009)
Lowell (2/6/2009)
i've got a stored proc i made that i named sp_getDDL, which trolls thru the table definition and it's columns and builds the script for the table; it's probably worthy of an article. I wrote it for sql2000 way back, and adapted it for 2005 as well.Or at least post it as a script. Writing a fairly complete "CREATE TABLE" proc is a herculean task, so I would love to have a copy.
Yup, god knows I tried it once.... everytime you think you're done something else pops up... then a new version comes around and you need to start right back over!
February 7, 2009 at 6:03 pm
Ninja's_RGR'us (2/7/2009)
RBarryYoung (2/6/2009)
Lowell (2/6/2009)
i've got a stored proc i made that i named sp_getDDL, which trolls thru the table definition and it's columns and builds the script for the table; it's probably worthy of an article. I wrote it for sql2000 way back, and adapted it for 2005 as well.Or at least post it as a script. Writing a fairly complete "CREATE TABLE" proc is a herculean task, so I would love to have a copy.
Yup, god knows I tried it once.... everytime you think you're done something else pops up... then a new version comes around and you need to start right back over!
Tell me about it. About 6 times in the past three years I have almost started to write a proc to do it, but then after I made an initial list of everything that I would have to do I would step back and say "No way! That's just crazy!" :w00t:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 7, 2009 at 8:21 pm
... and then you probably still missed some :P.
February 8, 2009 at 6:29 am
Ninja's_RGR'us (2/7/2009)
... and then you probably still missed some :P.
Definitely. Everytime I was at a different customer site and scripted a database, I would see something that I hadn't thought of: index filegroups, identity seed, fill factor, collations, partitions, etc., etc... The list of features that they have crammed into a single object type is incredible and seems endless.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy