Prevent users creating constraints or indexes with no name

Question

I have done some searching but haven't found a solution to my problem.

I started with some databases where users didn't care to name PK constraints, foreign keys or indexes, ending up with system generated names like PK__CarRenta__3213E83F2E5BD364. On mssqltips there's a great article by Aaron Bertrand about how to rename these objects using a stored procedure.

So that's great...but how do I avoid users or applications creating new objects without names which are then assigned a system generated name by SQL Server?

I have already read an article by the same great Aaron Bertrand on how to use policy based management to enforce naming conventions. However it seems that this is only good for notification after the horse has already bolted (or the object has been created).

Can you think of a way to automatically roll back statements that try to create an object without a name?

Answer 1

The simplest way to do this (though it makes me shudder a bit) is to use a database level DDL trigger with EVENTDATA. There is nothing specific here for constraints, so you'd have to use your imagination (depending on what you're attempting to actually accomplish) with the DDL Event groups, and some manual parsing. Here's an example to get you started.

CREATE DATABASE IndexRollback;
GO

USE Indexrollback;
GO

CREATE TRIGGER IndexNamingTrigger
ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX
AS
BEGIN

    -- this doesn't need to be here (can be inlined), just did it for readability
    DECLARE @ObjName sysname
    SELECT @ObjName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

    -- check our naming, if it doesn't start with IDX, throw it away and be super mean about it
    IF(LEFT(@ObjName, 3) <> 'IDX')
    BEGIN
        --log to a table or something
        PRINT 'Dont''t be daft, look at the naming convention spec!'
        ROLLBACK
    END
END
GO

CREATE TABLE Test
(
ID  INT NULL
)
GO

CREATE CLUSTERED INDEX IDX1 ON dbo.Test(ID)
GO
--success

CREATE NONCLUSTERED INDEX HAHAHAHAHA ON dbo.Test(ID)
GO
-- sad trombone