Enforce SQL Server Database Naming Conventions Using DDL Triggers
By: Aaron Bertrand | Comments | Related: More > Database Administration
Problem
In a previous tip, I showed how to enforce a database naming convention for existing databases using Policy-Based Management. In this tip, I want to show how to enforce a naming convention for databases using a simple DDL trigger.
Solution
For creating a new database, we can capture this activity using the CREATE_DATABASE
event in a DDL trigger. A very simple example is as follows:
USE [master]; GO CREATE TRIGGER PreventBadDatabaseNames ON ALL SERVER FOR CREATE_DATABASE AS BEGIN SET NOCOUNT ON; DECLARE @name NVARCHAR(255); SET @name = LOWER(EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)')); IF @name = N'foo' -- or @name LIKE N'%somepattern%' -- or @name IN (SELECT name FROM dbo.BadNameLibrary) BEGIN RAISERROR('You used a bad name [%s].', 11, 1, @name); ROLLBACK TRANSACTION; END END GO
Here I am just trapping for a specific "bad" name (foo
), but show how you could extend the rules to trap various pattern matches, or even use a table of known words. The LOWER()
is used to protect ourselves from case sensitive collations (just make sure that both sides of the clause use lower case).
Now, if I try to create a database named foo
, I will get this error:
Msg 50000, Level 11, State 1, Procedure PreventBadDatabaseNames You used a bad name [foo]. Msg 3609, Level 16, State 2 The transaction ended in the trigger. The batch has been aborted.
That takes care of the simple case - a user manually tries to create a database called foo
, and we stop them in their tracks. Now if only life were that simple...
Challenges
Of course, there are other ways to get a database named foo
without manually creating it. I can attach a database with that name, or I can change the name of a database (e.g. using sp_renamedb
, which is deprecated) that has already passed the tests in my DDL trigger.
Attaching a database
In this case, the DDL trigger fires for the CREATE_DATABASE
event, whether you use CREATE DATABASE ... FOR ATTACH[_REBUILD_LOG]
or sp_attach_db
(also deprecated). The problem is that, even though the trigger attempts to perform a rollback, the database remains created. You will see the same pair of error messages as above, but you will also notice that the database did, in fact, get created.
So we need to add a little logic that checks if the database exists, and detaches it again. We likely don't want to drop the new database, but rather have them try again with a name we want to accept. We can detach it using sp_detach_db
(not deprecated, by the way):
ALTER TRIGGER PreventBadDatabaseNames ON ALL SERVER FOR CREATE_DATABASE AS BEGIN SET NOCOUNT ON; DECLARE @name NVARCHAR(255); SET LOWER(@name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)')); IF @name = N'foo' -- or @name LIKE N'%somepattern%' -- or @name IN (SELECT name FROM dbo.BadNameLibrary) BEGIN RAISERROR('You used a bad name [%s].', 11, 1, @name); ROLLBACK TRANSACTION; IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @name) BEGIN EXEC sp_detach_db @dbname = @name; END END END GO
Now we can prevent bad database names from being attached as well.
Renaming a database
When you rename a database (using sp_renamedb
or ALTER DATABASE good_name MODIFY NAME = bad_name;
), the event that a DDL trigger will capture is ALTER_DATABASE
. Unlike with NewObjectName
for the RENAME
event on a database-level DDL trigger, there is no RENAME_DATABASE
event, nor is there a NewDatabaseName
attribute in the EventData()
XML. This is what the entire XML looks like for a rename:
<EVENT_INSTANCE> <EventType>ALTER_DATABASE</EventType> <PostTime>2014-01-01T00:00:00.000</PostTime> <SPID>51</SPID> <ServerName>BEAKER</ServerName> <LoginName>BEAKER\Aaron</LoginName> <DatabaseName>old_name</DatabaseName> <TSQLCommand> <SetOptions ... /> <CommandText>ALTER DATABASE [old_name] MODIFY NAME = [new_name]</CommandText> </TSQLCommand> </EVENT_INSTANCE>
You'll notice that the only place where new_name
appears is in the CommandText
property. This means that in order to check if the new name violates your convention, you need to parse and extract the name from the entire command. Adding to the challenge, the format of the command is quite predictable when using sp_renamedb
, but not so much when using ALTER DATABASE
. On the plus side, it doesn't appear possible to combine MODIFY NAME
with other ALTER DATABASE
options.
Here is a modified trigger that handles both CREATE_DATABASE
and ALTER_DATABASE
events. If the database is a new database, the transaction is rolled back, and a detach is attempted in the event that the rollback didn't actually prevent the creation. If the database is an existing database, we check to see that it is a rename event, and if the new name doesn't meet our naming standards, we name it back to the old name.
ALTER TRIGGER PreventBadDatabaseNames ON ALL SERVER FOR CREATE_DATABASE, ALTER_DATABASE AS BEGIN SET NOCOUNT ON; DECLARE @name NVARCHAR(255), @event NVARCHAR(255), @cmd NVARCHAR(MAX), @oldname NVARCHAR(255), @act BIT = 0; SELECT @name = LOWER(EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)')), @event = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(255)'), @cmd = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)'); IF @event = N'ALTER_DATABASE' AND LOWER(@cmd) LIKE N'%modify%name%=%' BEGIN SELECT @oldname = @name, @name = LOWER(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SUBSTRING(@cmd, CHARINDEX(N'=', @cmd) + 1, 255))),';',''),'[',''),']','')); END IF @name = N'foo' -- or @name LIKE N'%somepattern%' -- or @name IN (SELECT name FROM dbo.BadNameLibrary) BEGIN SET @act = 1; END IF @act = 1 BEGIN ROLLBACK TRANSACTION; IF @event = N'CREATE_DATABASE' BEGIN RAISERROR('You used a bad name [%s].', 11, 1, @name); IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @name) BEGIN EXEC sp_detach_db @dbname = @name; END END ELSE BEGIN -- name it back DECLARE @sql NVARCHAR(MAX) = N'ALTER DATABASE ' + QUOTENAME(@name) + ' MODIFY NAME = ' + QUOTENAME(@oldname) + ';'; EXEC sp_executesql @sql; -- optionally, you could raise an error message -- or print something here to let the user know END END END GO
There are some limitations here; for example, it assumes you don't intentionally place characters like =;[]
in the database name, and that you don't have a database named [modify-name=]
either. I also didn't test what happens if you try to rename a database that already exists (and happens to violate your convention); I envision an infinite loop of database name ping-pong. I suggest you ensure no existing database falls in this category before implementing this trigger; if any databases exist and can't easily be fixed, add exclusions to the logic in the DDL trigger.
Next Steps
- Decide on the rules your database names should conform to.
- Identify any existing databases that violate your rules.
- Implement a DDL trigger which will prevent future databases from violating your conventions.
- See the following tips and resources:
About the author

View all my tips