November 14, 2018 at 8:28 am
We would like the act of saving a T-SQL procedure or function to automatically append the name of the programmer + timestamp to an extended property of the object. Does anyone do something like this? Thanks for any suggestions.
November 14, 2018 at 8:58 am
Have you had a look at DDL triggers at all? I assume by "save" you mean if someone used the CREATE syntax? What about if the object was updated? Would you expect the previous information to be overwritten? Obviously if the object was dropped and created then it would be lost anyway.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 14, 2018 at 9:24 am
Not to be pedantic, but you're not saving the procedure or function. You're compiling it each time. If you want to do this, you can use a DDL trigger,but you need to handle a new procedure v an altered one. Do you update the values?
Note that the sys.objects DMV contains the timestamp for create and alter, so you don't need this, though I do understand wanting it in a separate place.
November 14, 2018 at 9:51 am
Your programmers ought to be in the habit of adding comments to an object definition every time they create or alter it. You can quality check this yourself, since you don't let them deploy directly to production, right?
John
November 14, 2018 at 10:07 am
ok, this looked fun, so i combined and modified a couple of techniques:
DROP TRIGGER TR_PopulateExendedEventOnEachObject
ON DATABASE
GO
--Our Trigger to limit our role:
CREATE TRIGGER TR_PopulateExendedEventOnEachObject
ON DATABASE
--FOR DDL_DATABASE_LEVEL_EVENTS
FOR CREATE_FUNCTION,ALTER_FUNCTION,CREATE_PROCEDURE,ALTER_PROCEDURE,CREATE_TABLE,ALTER_TABLE
AS
BEGIN
declare
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE VARCHAR(30),
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261),
@CMD VARCHAR(500)
--Load Variables from the xml
SET @eventData = eventdata()
--my standard variables for a DDL trigger are above,
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = CONVERT(VARCHAR(30),GETDATE(),120),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
IF OBJECT_ID(@OBJECTNAME) IS NOT NULL AND NOT EXISTS(SELECT
*
FROM FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', @SCHEMANAME, @OBJECTTYPE, @OBJECTNAME, NULL, NULL)X
WHERE name = 'LastModifiedWhodunnit')
BEGIN
EXEC sys.SP_ADDEXTENDEDPROPERTY
@name='LastModifiedWhodunnit',-- Name of the new property
@value=@EVENTDATE,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@SCHEMANAME,--Schema Name
@level1type=@OBJECTTYPE,-- Object Type (Procedure, Function)
@level1name=@OBJECTNAME --SP/FN Name
END
ELSE
BEGIN
EXEC sys.SP_UPDATEEXTENDEDPROPERTY
@name='LastModifiedWhodunnit',
@value=@EVENTDATE,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@SCHEMANAME,--Schema Name
@level1type=@OBJECTTYPE,-- Object Type (Procedure, Function)
@level1name=@OBJECTNAME --SP/FN Name
END
IF OBJECT_ID(@OBJECTNAME) IS NOT NULL AND NOT EXISTS(SELECT
*
FROM FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', @SCHEMANAME, @OBJECTTYPE, @OBJECTNAME, NULL, NULL)X
WHERE name = 'LastWhodunnit')
BEGIN
EXEC sys.SP_ADDEXTENDEDPROPERTY
@name='LastWhodunnit',-- Name of the new property
@value=@ORIGINALUSER,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@SCHEMANAME,--Schema Name
@level1type=@OBJECTTYPE,-- Object Type (Procedure, Function)
@level1name=@OBJECTNAME --SP/FN Name
END
ELSE
BEGIN
EXEC sys.SP_UPDATEEXTENDEDPROPERTY
@name='LastWhodunnit',
@value=@ORIGINALUSER,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@SCHEMANAME,--Schema Name
@level1type=@OBJECTTYPE,-- Object Type (Procedure, Function)
@level1name=@OBJECTNAME --SP/FN Name
END
END --trigger
GO
--enable the trigger
ENABLE TRIGGER TR_PopulateExendedEventOnEachObject ON DATABASE
GO
IF OBJECT_ID('[dbo].[AnimalNamesx]') IS NOT NULL
DROP TABLE [dbo].[AnimalNamesx]
SELECT 1 AS AnimalID,'Frogs' AS AnimalName
INTO dbo.AnimalNamesx
UNION ALL
SELECT 2,'Horses'
SELECT * FROM sys.[extended_properties] WHERE [major_id] = OBJECT_ID('[dbo].[AnimalNamesx]')
Lowell
November 14, 2018 at 2:20 pm
rchantler - Wednesday, November 14, 2018 8:28 AMWe would like the act of saving a T-SQL procedure or function to automatically append the name of the programmer + timestamp to an extended property of the object. Does anyone do something like this? Thanks for any suggestions.
Would it be more useful to track the changes into a separate table, so all changes are logged in a central area ?
You would also have history, instead of just the most recent change.
November 14, 2018 at 6:23 pm
homebrew01 - Wednesday, November 14, 2018 2:20 PMrchantler - Wednesday, November 14, 2018 8:28 AMWe would like the act of saving a T-SQL procedure or function to automatically append the name of the programmer + timestamp to an extended property of the object. Does anyone do something like this? Thanks for any suggestions.Would it be more useful to track the changes into a separate table, so all changes are logged in a central area ?
You would also have history, instead of just the most recent change.
That's what we do. And, it get's checked into SVN.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2018 at 7:46 am
Lowell - Wednesday, November 14, 2018 10:07 AMok, this looked fun, so i combined and modified a couple of techniques:
DROP TRIGGER TR_PopulateExendedEventOnEachObject
ON DATABASE
GO
--Our Trigger to limit our role:
CREATE TRIGGER TR_PopulateExendedEventOnEachObject
ON DATABASE
--FOR DDL_DATABASE_LEVEL_EVENTS
FOR CREATE_FUNCTION,ALTER_FUNCTION,CREATE_PROCEDURE,ALTER_PROCEDURE,CREATE_TABLE,ALTER_TABLE
AS
BEGIN
declare
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE VARCHAR(30),
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261),
@CMD VARCHAR(500)
--Load Variables from the xml
SET @eventData = eventdata()
--my standard variables for a DDL trigger are above,
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = CONVERT(VARCHAR(30),GETDATE(),120),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')IF OBJECT_ID(@OBJECTNAME) IS NOT NULL AND NOT EXISTS(SELECT
*
FROM FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', @SCHEMANAME, @OBJECTTYPE, @OBJECTNAME, NULL, NULL)X
WHERE name = 'LastModifiedWhodunnit')
BEGIN
EXEC sys.SP_ADDEXTENDEDPROPERTY
@name='LastModifiedWhodunnit',-- Name of the new property
@value=@EVENTDATE,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@SCHEMANAME,--Schema Name
@level1type=@OBJECTTYPE,-- Object Type (Procedure, Function)
@level1name=@OBJECTNAME --SP/FN Name
END
ELSE
BEGIN
EXEC sys.SP_UPDATEEXTENDEDPROPERTY
@name='LastModifiedWhodunnit',
@value=@EVENTDATE,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@SCHEMANAME,--Schema Name
@level1type=@OBJECTTYPE,-- Object Type (Procedure, Function)
@level1name=@OBJECTNAME --SP/FN Name
END
IF OBJECT_ID(@OBJECTNAME) IS NOT NULL AND NOT EXISTS(SELECT
*
FROM FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', @SCHEMANAME, @OBJECTTYPE, @OBJECTNAME, NULL, NULL)X
WHERE name = 'LastWhodunnit')
BEGIN
EXEC sys.SP_ADDEXTENDEDPROPERTY
@name='LastWhodunnit',-- Name of the new property
@value=@ORIGINALUSER,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@SCHEMANAME,--Schema Name
@level1type=@OBJECTTYPE,-- Object Type (Procedure, Function)
@level1name=@OBJECTNAME --SP/FN Name
END
ELSE
BEGIN
EXEC sys.SP_UPDATEEXTENDEDPROPERTY
@name='LastWhodunnit',
@value=@ORIGINALUSER,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@SCHEMANAME,--Schema Name
@level1type=@OBJECTTYPE,-- Object Type (Procedure, Function)
@level1name=@OBJECTNAME --SP/FN Name
END
END --trigger
GO
--enable the trigger
ENABLE TRIGGER TR_PopulateExendedEventOnEachObject ON DATABASE
GOIF OBJECT_ID('[dbo].[AnimalNamesx]') IS NOT NULL
DROP TABLE [dbo].[AnimalNamesx]SELECT 1 AS AnimalID,'Frogs' AS AnimalName
INTO dbo.AnimalNamesx
UNION ALL
SELECT 2,'Horses'
SELECT * FROM sys.[extended_properties] WHERE [major_id] = OBJECT_ID('[dbo].[AnimalNamesx]')
Thanks Lowell, that's great!
November 15, 2018 at 7:50 am
Jeff Moden - Wednesday, November 14, 2018 6:23 PMThat's what we do. And, it get's checked into SVN.
We have an automated process that runs daily and checks all changed into VSS. Unfortunately it doesn't log who made the change; because it's daily it could have been changed by more than one individual. However, changes are rare, so I thought an extended property that holds the list of changes would be helpful. I'm not saying this is better than a central table, just finishing my original thought.
November 15, 2018 at 4:52 pm
rchantler - Thursday, November 15, 2018 7:50 AMWe have an automated process that runs daily and checks all changed into VSS. Unfortunately it doesn't log who made the change; because it's daily it could have been changed by more than one individual. However, changes are rare, so I thought an extended property that holds the list of changes would be helpful. I'm not saying this is better than a central table, just finishing my original thought.
Ah... just in case... my apologies... I wasn't taking any shots at anyone else's methods. I do, in fact, like extended properties a whole lot, as well. It's a form of documentation that makes doing things like producing a data dictionary on demand quite the snap.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2018 at 8:28 am
Jeff Moden - Thursday, November 15, 2018 4:52 PMAh... just in case... my apologies... I wasn't taking any shots at anyone else's methods. I do, in fact, like extended properties a whole lot, as well. It's a form of documentation that makes doing things like producing a data dictionary on demand quite the snap.
Thanks but no offence was taken! And we are a small shop of accidental DBAs and appreciate the good information.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply