Enforcing Object Naming Rules With a DDL Trigger

A naming rule that says procedures begin with usp and indexes begin with IX_ is easy to ignore during a deadline. A database-level DDL trigger can reject new names with a clear message. Pair it with an audit trail, because a rejected DDL statement rolls back any table log written by that same trigger.

Define a Rule With Exceptions

A rule should be precise enough that developers can predict it. Decide whether system-created primary key and unique-constraint indexes are exempt, whether case matters, and how legacy objects are handled. The example watches explicit CREATE_PROCEDURE and CREATE_INDEX events. It does not retroactively rename old objects or catch every route that creates an index, such as a constraint added through ALTER TABLE.

I start by listing current violations before enabling enforcement. What would a deployment tool create tomorrow that the trigger would reject? Test that path in a restored database and document an exception process rather than making developers discover it during a release.

Read EVENTDATA in a DDL Trigger

EVENTDATA returns XML for the DDL event, including EventType, ObjectName, LoginName, and command text. For CREATE_INDEX, ObjectName names the index in the tested event shape. The trigger checks a procedure's prefix and an index's prefix, then raises a readable error and rolls back the DDL statement.

SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER TRIGGER EnforceObjectNames
ON DATABASE
FOR CREATE_PROCEDURE, CREATE_INDEX
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @event xml = EVENTDATA();
    DECLARE @kind sysname =
        @event.value('(/EVENT_INSTANCE/EventType)[1]','sysname');
    DECLARE @name sysname =
        @event.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname');
    IF (@kind = N'CREATE_PROCEDURE'
        AND @name NOT LIKE N'usp%')
       OR (@kind = N'CREATE_INDEX'
        AND @name NOT LIKE N'IX[_]%')
    BEGIN
        RAISERROR('Name rejected: procedures need usp and indexes need IX_.',16,1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;
GO

Run this first in a disposable database. A DDL trigger runs in the transaction of the statement that fired it and can block deployments. Keep the logic short and have an administrative disable procedure if a defect locks out valid changes. The index pattern uses [_] to match a literal underscore in LIKE. Leave the schema prefix off the trigger name; SQL Server rejects dbo. on a database-level trigger with Msg 1094. The XML methods also need QUOTED_IDENTIFIER ON when the trigger is created. SSMS sets it by default; sqlcmd does not unless you pass -I.

Test Allowed and Rejected Names

Create one procedure and index with accepted names, then attempt names that violate the rule. Verify the rejected objects do not exist. Capture the user-facing message; it should tell a developer how to correct the name. Test under the same deployment login used by releases. The last statement below fails on purpose.

CREATE OR ALTER PROCEDURE dbo.uspAllowedDemo
AS SELECT 1 AS value;
GO
CREATE TABLE dbo.NamingDemo (ID int NOT NULL);
GO
CREATE INDEX IX_NamingDemo_ID ON dbo.NamingDemo(ID);
GO
-- In a separate lab batch, expect rejection:
CREATE INDEX BadName ON dbo.NamingDemo(ID);
GO

The trigger watches CREATE_PROCEDURE, not every ALTER of an existing procedure. Extend it only after checking EVENTDATA values for each added event and exception. A naming policy that rejects an emergency fix to a legacy procedure can do more harm than good.

Log Attempts Outside the Rolled-Back Transaction

An INSERT into a normal table from the DDL trigger is in the same transaction as the DDL. If the trigger rolls back a bad name, that INSERT rolls back too. Therefore, a trigger-only table cannot durably log every rejected attempt. Use SQL Server Audit to record attempted schema-object changes independently, and use the trigger only for enforcement. Configure an audit file on a protected path and a database audit specification for the relevant schema-object change group, then test both a committed and rejected CREATE.

-- Example audit setup in a lab; grant the service account file access.
USE master;
GO
CREATE SERVER AUDIT NamingAudit
TO FILE (FILEPATH = 'D:\SqlAudit\');
ALTER SERVER AUDIT NamingAudit WITH (STATE = ON);
GO
USE [YourDatabase];
GO
CREATE DATABASE AUDIT SPECIFICATION NamingAuditSpec
FOR SERVER AUDIT NamingAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

The server audit must be created in master, so the script switches there first and then back to your database. Audit configuration needs administrative review, file retention, and failure behavior. Read the audit file after the test and confirm it captured the events required by the policy. If a failed statement is missing under this action group on your build, add the right audit action or an Extended Events error capture, then verify again. Do not claim comprehensive logging from an untested trigger table.

List Existing Violations

The trigger is forward-looking. Query existing procedures and indexes for names outside the rule. Exclude system objects and constraint-backed indexes according to the approved policy. An existing violation is an inventory item, not an automatic rename; dependencies can refer to the current object name.

SELECT N'PROCEDURE' AS object_type,
       SCHEMA_NAME(schema_id) AS schema_name, name
FROM sys.procedures
WHERE is_ms_shipped = 0 AND name NOT LIKE N'usp%'
UNION ALL
SELECT N'INDEX', OBJECT_SCHEMA_NAME(i.object_id), i.name
FROM sys.indexes AS i
JOIN sys.objects AS o ON o.object_id = i.object_id
WHERE o.is_ms_shipped = 0 AND i.name IS NOT NULL
  AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
  AND i.name NOT LIKE N'IX[_]%';

Plan legacy cleanup separately. I keep a report of new attempted violations, accepted exceptions, and current old names. The trigger's purpose is to keep the list from growing while owners resolve the past at a safe pace.

Keep the DDL Trigger Maintainable

Test trigger behavior with deployment tooling, scripted constraints, and schema migrations. Document the allowed prefixes and exact exceptions in plain language. Monitor audit file health and space; a logging failure can leave an enforcement gap. Review the trigger after version upgrades because EVENTDATA shapes and new statement forms can change the paths people use.

A short trigger with a separate audit is easier to trust than a large parser inside SQL Server. The rule should improve consistency without trapping the team during recovery. A clear rejected-name message, a tested escape procedure, and an independent attempt log make that possible.

Do Not Lose a Rejected Attempt

The trigger's rollback is the key logging trap. Even an INSERT into a different user database remains part of the same transaction and rolls back with the DDL. An audit file or event capture sits outside that transaction and can preserve evidence. Test the failed-event path rather than assuming the Audit action group records precisely what you need. Read the file with the approved audit reader and compare timestamp, principal, database, statement, and success flag.

A normal table log inside the trigger can still record accepted changes, but it is redundant if SQL Server Audit already captures them. Keep the design simple. If the audit target becomes unavailable, the organization's configured audit failure policy determines whether operations continue; decide that policy before enabling enforcement.

Stop the DDL Trigger From Blocking Recovery

The trigger can fire during maintenance, upgrades, or recovery scripts. Create an explicit exception process controlled by administrators, with a short change record and after-the-fact audit review. Do not create a broad exemption for all privileged logins, because then the highest-impact changes bypass the standard silently. Test disabling and re-enabling the trigger on the lab database before an incident demands it. Save the audit record for that exception and confirm normal enforcement resumes immediately afterward. The recovery path needs an owner, not an undocumented bypass.

Related reading on this blog: Who ALTER'ed My Database? Catch Them Via DDL Trigger and Trigger on Database to Prevent Table Creation.

A naming trigger is not an independent audit, it is enforcement whose rejected work rolls back.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.