3

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?

  • Are you sure that using policy based management you cannot prevent someone from creating the object without the name? – SqlWorldWide Feb 7 '18 at 19:22
  • 1
    You can't create unnamed indexes AFAIK. Only unnamed constraints (which may have an associated index) – Martin Smith Feb 7 '18 at 21:13
  • @MartinSmith: You are right...indexes have to be given a name during creation...constraints not whatsoever. – Martin Guth Feb 8 '18 at 9:05
9

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
  • Thank you for this example...I'll try to adapt it for unnamed constraints. – Martin Guth Feb 8 '18 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.