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?

• SqlWorldWide: Are you sure that using policy based management you cannot prevent someone from creating the object without the name?
• Martin Smith: You can't create unnamed indexes AFAIK. Only unnamed constraints (which may have an associated index)
• Martin Guth: @MartinSmith: You are right...indexes have to be given a name during creation...constraints not whatsoever.
• i-one: Exactly what you need is solution based on use of DDL trigger, see here

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

• Martin Guth: Thank you for this example...I'll try to adapt it for unnamed constraints.