How do I capture the new procedure definition after a rename

Question

I am implementing a simple version control/audit for code changes, and I have DDL triggers to capture CREATE, ALTER, and DROP events for Procs, Functions, Views, and Triggers. These are all working properly to maintain a history of the code changes in a repository table. However, when I add a trigger for RENAME events, I can grab the definition from sys.sql_modules or OBJECT_DEFINITION(), but the name of the object is the still the old name in the definition. On sys.objects.name, the name has been changed, but the metadata for the object definition is unchanged. I tried calling sp_recompile on the object, but the metadata is still not refreshing. If I open the proc in SSMS, and then look at the metadata, the definition has changed.

Is there anyway to force the metadata to refresh after sp_rename (the actual TSQLCommand in the event data)?

Here is my DDL trigger for object renames (I am logging a rename as a DROP of the original object and a CREATE for the newly renamed object)

CREATE TRIGGER [trgVersionControl_Rename]
ON  DATABASE
FOR  RENAME
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData      xml
    SET @EventData=EVENTDATA()

    DECLARE @Schema NVARCHAR(256) = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)');
    DECLARE @Object NVARCHAR(256) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');
    DECLARE @NewName NVARCHAR(256) = @EventData.value('(/EVENT_INSTANCE/NewObjectName)[1]', 'nvarchar(max)');
    DECLARE @Command NVARCHAR(256) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');

    INSERT INTO VersionControl..Repository(DatabaseName, SchemaName, ObjectName, [Definition], Operation, ObjectType)
    SELECT
        DB_NAME(), s.name, @Object, NULL, 'DROP', CASE o.[type] WHEN 'P' THEN 'PROCEDURE' WHEN 'V' THEN 'VIEW' WHEN 'TR' THEN 'TRIGGER' ELSE 'FUNCTION' END
        FROM sys.objects o
            INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
            INNER JOIN sys.sql_modules   m ON o.object_id=m.object_id
        WHERE o.Name=@NewName
            AND s.name=@Schema
            AND o.type IN ('P', 'V', 'TF', 'FN', 'TR')

    INSERT INTO VersionControl..Repository(DatabaseName, SchemaName, ObjectName, [Definition], Operation, ObjectType)
    SELECT
        DB_NAME(), s.name, @NewName, m.[definition], 'CREATE', CASE o.[type] WHEN 'P' THEN 'PROCEDURE' WHEN 'V' THEN 'VIEW' WHEN 'TR' THEN 'TRIGGER' ELSE 'FUNCTION' END
        FROM sys.objects o
            INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
            INNER JOIN sys.sql_modules   m ON o.object_id=m.object_id
        WHERE o.name = @NewName
            AND s.name=@Schema
            AND o.type IN ('P', 'V', 'TF', 'FN', 'TR')

END
GO

ENABLE TRIGGER [trgVersionControl_Rename] ON DATABASE
GO

Answer 1

SSMS uses the client SMO library to properly script the definition of the renamed object with the current name. There is no T-SQL solution (at least reliable one) that will change the object definition meta-data.

IMHO, your schema change log should track a SQL module rename operation as a RENAME operation rather than DROP/CREATE for a proper audit log.

Version control is a separate function. Reverse-engineering modules to facilitate version control should be done using SMO rather than T-SQL. That's essentially what SSDT and third-party tools do.

Answer 2

See https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver15#remarks

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.