3 comments

  • Nice job. Good idea and great post.

  • I actually tried to do this exact thing before coming across your post. Our code looks pretty similar, however when I try to create a table I get the message “Property cannot be added. Property ‘MS_Creator’ already exists for ‘dbo.Table_1’.” (where MS_Creator is the ‘name’ of the EP). That’s what prompted me to search the web and came across your post. My code is below, any thoughts would be appreciated.

    USE [master];
    GO

    IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = ‘ddl_trigger_table_create_alter_WhoDidIt’)
    DROP TRIGGER ddl_trigger_table_create_alter_WhoDidIt
    ON ALL SERVER;
    GO
    CREATE TRIGGER ddl_trigger_table_create_alter_WhoDidIt
    ON ALL SERVER

    FOR CREATE_TABLE, ALTER_TABLE
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE
    @EventData XML = EVENTDATA();

    –insert SandBox.dbo.EventDataFromDDL (eventdataxml)
    — SELECT
    — @EventData ;

    DECLARE @TableName SYSNAME; — Name of table
    set @TableName = @EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘NVARCHAR(255)’);
    DECLARE @dbName NVARCHAR(200); — Name of table
    set @dbName = @EventData.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘NVARCHAR(255)’);
    DECLARE @SchemaName NVARCHAR(200); — Name of table
    set @SchemaName = @EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘NVARCHAR(255)’);
    Declare @LoginName nvarchar(200);
    set @LoginName = @EventData.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘NVARCHAR(255)’);

    DECLARE @MS_Description NVARCHAR(200) = NULL;
    DECLARE @module_name_var NVARCHAR(500) = QUOTENAME(@DbName) + ‘.sys.extended_properties’;
    DECLARE @module_name_var_add NVARCHAR(500) = QUOTENAME(@DbName) + ‘.sys.sp_addextendedproperty’;
    DECLARE @module_name_var_upd NVARCHAR(500) = QUOTENAME(@DbName) + ‘.sys.sp_updateextendedproperty’;

    declare @sql nvarchar(1000)

    set @sql= N’SET ‘ + @MS_Description + ‘ = (SELECT CAST(Value AS NVARCHAR(200)) AS [MS_Creator]
    FROM ‘ + @module_name_var + ‘AS ep
    WHERE ep.major_id = OBJECT_ID(‘ + @TableName + ‘)
    AND ep.name = N”MS_Creator” AND ep.minor_id = 0); ‘

    exec sp_executesql @sql

    IF @MS_Description IS NULL
    BEGIN
    EXEC @module_name_var_add
    @name = N’MS_Creator’,
    @value = @LoginName,
    @level0type = N’SCHEMA’,
    @level0name = @SchemaName,
    @level1type = N’TABLE’,
    @level1name = @TableName;
    END
    ELSE
    BEGIN
    EXEC @module_name_var_upd
    @name = N’MS_Creator’,
    @value = @LoginName,
    @level0type = N’SCHEMA’,
    @level0name = @SchemaName,
    @level1type = N’TABLE’,
    @level1name = @TableName;
    END
    END
    GO

Leave a Reply to Thomas Schütte Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.