Enforce SQL Server Database Naming Conventions Using Policy Based Management

By:   |   Comments (10)   |   Related: > Policy Based Management


In many environments you may want to enforce specific database naming conventions. For example, you may want to prevent others from naming their databases using special characters like dash or period. These and other poor naming decisions can wreak havoc on both internal and custom modules that (perhaps wrongly) make assumptions about database names.


While it is not always easy to catch these issues at the time the name is implemented, Policy-Based Management can at least help identify the databases that already don't conform to your rules. A common and sensible convention for databases would likely include the following rules about the name:

  • does not include any special characters (dash, period);
  • does not start with a number;
  • does not have embedded or trailing spaces; and,
  • is no longer than 16 characters (let's pretend we're not using SharePoint).

First, let's create some sample databases that will certainly violate these rules, by running the following code:

CREATE DATABASE [1_start_number];
CREATE DATABASE [embedded space];
CREATE DATABASE [embedded.period];
CREATE DATABASE [embedded-dash];
CREATE DATABASE [trailing_space ];
CREATE DATABASE [this_dbname_is_17];

To represent the above rules in T-SQL (and to identify any violations manually), we can simply create the following LIKE construct and query sys.databases:

SELECT name FROM sys.databases 
  WHERE name LIKE N'%[- .]%' -- contains dash, space, or period 
  OR name LIKE N'[0-9]%'     -- starts with a number
  OR LEN(name) > 16;         -- is greater than 16 characters 

But then we can quickly think of several other characters that can cause problems in different scenarios: pipe, apostrophe, quote, tilde, curly brace, backslash... the list goes on. So, as long as you do not need to support Unicode-only characters in a database name, it is likely going to be easier to identify the characters that you want to allow, rather than identifying the characters you don't want to allow. In my environments, databases can be named with US English alphanumeric characters and underscores, can't begin with a number, and must have 16 or fewer characters. In this case, the above rule can be simplified as follows:

SELECT name FROM sys.databases
  WHERE name LIKE N'%[^A-Za-z0-9[_]]%' -- has any char besides A-Z, a-z, 0-9, underscore
  OR name LIKE N'[0-9]%'               -- starts with a number
  OR LEN(name) > 16;                   -- is greater than 16 characters

(If you need to support Unicode characters in the name, be prepared to manually list out all of the characters you want to allow. Also to support case sensitive collations both upper and lower case A-Z must be listed.)

Create the Condition and Policy

In Policy-Based Management, a policy is supposed to be expressed in terms of the desired state - we want policies to succeed only if the positive conditions are met, otherwise we are dealing with mixed messages if a policy only succeeds because our desired intent was not met. The above query finds all of the databases that are NOT in our desired state (do not conform to our naming convention). To make it so that it successfully finds the databases that DO conform to our naming convention, we need to change LIKE to NOT LIKE, greater than to less than or equal to, and combine the expressions using AND instead of OR - because instead of failing due to meeting ANY of our "bad" conditions, we only want it to succeed if it meets ALL of our "good" conditions. So we can re-write the above query to verify that it does not return any of the poorly-named databases we've created:

SELECT name FROM sys.databases 
  WHERE name NOT LIKE N'%[- .]%' -- does not contain dash, space, or period 
  AND name NOT LIKE N'[0-9]%'    -- does not start with a number
  AND LEN(name) <= 16;           -- is less than or equal to 16 characters 

When we create the condition and policy, since we want each database to adhere to our naming convention, we should call the condition something like, "Database adheres to naming convention." In Object Explorer, go to Management > Policy Management, right-click Conditions, and select "New Condition..."

using policy-based management to identify databases that don't conform to your rules about name

In the Create New Condition dialog, enter "Database adheres to naming convention" in the Name: field and change the Facet: to Database. Then we will add three expressions, each representing one of the WHERE clauses from above. (Note that in Conditions, string operators do not support the leading N prefix to denote Unicode strings, nor do they support the ESCAPE sequence for LIKE / NOT LIKE predicates.)

    For expression 1, change Field to "@Name", Operator to "NOT LIKE", and enter the value:
    '%[- .]%'

    For expression 2, make sure AndOr is "AND", then change Field to "@Name", Operator to "NOT LIKE", and enter the value:


    Expression 3 is the trickiest one - you can't use functions like LEN() on the @Name property in a simple expression, and unlike built-in properties like @@ObjectName, there is no property such as @@DatabaseName exposed to PBM. But since the condition will be executed in the context of each database, so if you use ExecuteSql(), you can use LEN() against built-in functions like DB_NAME(). So for this expression, make sure AndOr is "AND", change Operator to "<=", Value to "16", and then click on the [...] button to enter the following expression into the Advanced Edit dialog:

    ExecuteSql('Numeric', 'SELECT LEN(DB_NAME())')

    Click OK. The Create New Condition dialog should now look like this:

    the create new condition dialog
Click OK to save the condition.

To create a policy that evaluates this condition, go back to Object Explorer, and under Management > Policy Management, right-click Policies and choose "New Policy..."

go to object explorer under policies and choose new policy

In the Create New Policy dialog, enter "All databases should adhere to naming convention" in the Name: field and change the Check condition: property to the condition you created above. The rest of the properties can remain at their defaults so that we can head straight to testing the policy. The dialog should look like this:

all databases should adhere to naming convention

Click OK to save the Policy.

Evaluate the Policy

To evaluate the policy, you can go to Object Explorer, expand Management > Policy Management > Policies, right-click the Policy, and choose Evaluate. That will bring up this dialog, which includes a warning about any policy that includes functions like ExecuteSql():

evaluate the policy

Click Evaluate. This will bring up another dialog, asking once again to confirm that your scripts are trustworthy. Unless you routinely download policies from random web sites, feel free to check the "Do not show this message again" checkbox:

policy evaluation security notice

Click Run. If you have any of the databases above that violate your naming convention, they should appear in the list with a red X indicating failure:

any databases that violate the naming convention will appear

The default layout of the results makes it difficult to visually parse the error conditions and identify which databases violated the policy and why. You can click on the "View..." link in each row, and see exactly which part of the condition caused each of the databases to fail. Here is the result for a database that violated the "starts with a number" clause of the condition:

click on the view link to see which databases violated the policy and why

And here is the result for a database that violated the "exceeds 16 characters" clause of the condition:

you can automate the policy by changinging its evaulation mode to on schedule

As you can see, the detailed view will show exactly which clause(s) of the condition caused the policy evaluation to fail - there may have been more than one issue with the database name, and the Result column will reflect those failures.

Automate the Policy

You can automate the policy by changing its evaluation mode to "On schedule" - this way, you can be alerted hourly, daily, weekly or monthly if anyone has created a poorly-named database in that time period. You can even export the policy and deploy it to multiple servers, as Edwin Sarmiento explains in his tip, Evaluating and Implementing Policies in Multiple Instances in SQL Server 2008. For more advanced automation of policies throughout your Enterprise, you can look into the CodePlex project, Enterprise Policy Management Framework.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, September 22, 2017 - 4:53:16 AM - Darek Back To Top (66525)

To those that have had problems implementing the rule that states SELECT * is not allowed... I've done this recently using SQL# (the free version is enough for most purposes, just google for it) and DDL server level triggers. It's a matter of knowing the regular expression pattern. The general pattern should take into consideration the fact that you could also have something like SELECT TOP() *. So, instead of creating one pattern for all the possibilities I created two triggers - one disallows using TOP without parentheses after it (TOP should never be written without parenths after it as it's not standard compliant), the second one tackles all the possibilities for SELECT * (incorporating the case with TOP(...)). The pattern for TOP is this: N'select\s+top\s+[^(\s]'; (this regex looks for TOP without parenths after it); the one for SELECT * is this: N'select\s+(\*|top\s*\([^)]+\)\s*\*)' (the pattern looks for SELECTs with a star after it). This does seem to work OK for the time being. If somebody creates a piece of code in a function, stored proc, view... with the antipatterns inside, the trigger rolls the changes back with an error.


Thursday, October 24, 2013 - 2:07:08 PM - Shirley Back To Top (27271)

How do you run this policy for only new databases? I tried the batching with the Or logic and I get an email everyday that it fails. However, when I evaluate it, it is successful.


I have the above in the condition,  batched with:

Create Date>10/1/13

then the OR statement create date<10/01/13.

Any assistance appreciated.

Wednesday, October 17, 2012 - 9:14:53 AM - Aaron Bertrand Back To Top (19959)

@Ned that's probably true, but I wanted to demonstrate the ExecuteSQL() function regardless.

Wednesday, October 17, 2012 - 7:26:25 AM - Ned Back To Top (19953)

For the third expression LEN(@NAME) in the field works fine rather than the ***bersome ExecuteSQL statement.



Thursday, March 15, 2012 - 3:07:57 PM - Matthew Darwin Back To Top (16412)

I've had a play with that and it still seems to go through. I'm wondering if it's do with escape characters and the way the expressions are created in pbm, and perhaps the way in which the actual trigger is then built to prevent the table creation; possibly some kind of dynamic SQL nightmare? Though if that were the case you'd think it would fall over completely rather than just let it through.

I'll have a good play tomorrow and if I get it working will let you know.


Thursday, March 15, 2012 - 1:34:18 PM - Aaron Bertrand Back To Top (16411)

Hi Matthew, I'm not sure why the apostrophe would be slipping through the existing conditions, but you could try adding an additional condition, e.g.

@Name NOT LIKE '%''%';

Thursday, March 15, 2012 - 1:19:44 PM - Matthew Darwin Back To Top (16410)

Apologies for digging up a fairly old article, but currently this is the most pertinent thing close to what I'm looking at.

I've been trying to set up a policy to prevent creating tables in a similar fashion to the above policy for database names, however, I'm very much struggling to make it work when it comes to apostrophes.

The condition I've been using is identical to the second condition highlighted above - "@name NOT LIKE '%[^A-Za-z0-9[_]]%'.

The policy works perfectly if I try and run the following statement:-

CREATE TABLE dbo.[Break Policy]
(ID int identity(1,1())

However, if I add an apostrophe to the create table statement it will allow the creation statement to complete without a hitch:-

CREATE TABLE dbo.[Break' Policy]
(ID int identity(1,1())

Any idea how to make this work, or should I just be going back to DDL triggers?

Friday, February 11, 2011 - 9:24:05 AM - David McKinney Back To Top (12917)



The article is a neat idea, but we could never implement it.  However in the comments related to the article, i found a link to an article by Thomas LaRock which points out a column selall in sys.sysdepends, and which leads to is_select_all in sys.sql_dependencies.  This pretty much meets my requirements.  I'm gobsmacked!  What a strange coincidence!

As for "exists (select 1...", I agree with you.  I've never come across "exists (select null". Does that do something different?

Anyway, thanks for pointing me in the right direction!





so.name, schema_name(so.schema_id) FROM sys.sql_dependencies sd



JOIN sys.objects so on sd.object_Id=so.object_id




Friday, February 11, 2011 - 8:31:54 AM - Aaron Bertrand Back To Top (12913)

Actually, there was an article about preventing SELECT * over on SQLServerCentral this morning:


Not sure I like the implementation or the side effects, but it's one possibility.  If you want to avoid the changes to schema you could enforce access to tables through views and add an inaccessible but no-op column there.

As for parsing for SELECT * and having to allow it within EXISTS, why not establish a policy of using SELECT 1 or SELECT NULL inside of EXISTS?

Anyway, just some thoughts.

Friday, February 11, 2011 - 3:19:11 AM - David McKinney Back To Top (12911)

I'm trying - with mixed results - to implement PBM for a variety of development standards.  For example, it is a standard where I work that all accesses outside of the current database be done via synonyms.  Another policy I've set up is that there are no 3 part names with the schema missing (such as MyDB..MyTable.  Another is that stored procedure calls should not be caller dependant i.e. the schema name is always specified.  So you can implement some fairly complex standards with a little imagination.  But the first standard that we wanted to implement is that there should be no 'Select *' in our code (except perhaps in '... exists (select * FROM....').  This at first glance simple requirement has proved to be elusive to the point that I'm going to wait for someone / some vendor to tackle this for me.

In general, what surprises me most is that apparently this tool has been targeted exclusively at the DBA, with no consideration of development standards - and as a result I'm apparently alone in trying to enforce development standards in this way.

So thanks for letting me know that I'm not totally on my own!

get free sql tips
agree to terms