Sql-server – How to Apply a Title-Case RegEx Condition for Object Names with Policy Based Management

best practicesnaming conventionregular expressionsql server

I'm implementing PBM on a SQL Server 2014 instance and want to enforce a condition that all objects are created outside the dbo schema and all object names are created in title-case (i.e., OrderDetail). I have the regular expression drafted [A-Z]{1}[a-z]+[A-Z]?[a-z]* but am not able to create a satisfactory object name with it. An example output is here.

Policy condition: '@Schema != 'dbo' AND @Name LIKE '[A-Z]{1}[a-z]+[A-Z]?[a-z]*''
Policy description: ''
Additional help: '' : ''
Statement: 'CREATE TABLE Test.OrderDetail (
Col1 int)

Is this particular expression not supported by PBM or is there alternate syntax I should use in the condition?

Best Answer

SQL Server's implementation of the Like operator only supports a really cutdown kind of regEx which includes basic character matching and wildcards, but does not include Number of repeats like {1} or the other kind regEx anchors like \b or \s. It is also generally speaking, not case-sensitive.

That said, you can sometimes spoof these kind of things with Like. Say you wanted to find 3 alpha characters and two numbers you could do something like:

[a-z][a-z][a-z][0-9][0-9]

You can also force case-sensitve matches by using a case-sensitive collation, eg

DECLARE @s VARCHAR(10) = 'orderDetail'

-- Check case; 
SELECT
    CASE
        WHEN UPPER( LEFT( @s, 1 ) ) = LEFT( @s, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1 
        ELSE 0
    END

SET @s = 'OrderDetail'

-- Check case; 
SELECT
    CASE
        WHEN UPPER( LEFT( @s, 1 ) ) = LEFT( @s, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1 
        ELSE 0
    END

However, isn't TitleCase more of a philosophical position than a policy? How would you determine whether TitleCase or Titlecase is correct? In fact they both are.

Your particular regEx pattern would also match 'orderDetails' and the 'Details' secton of 'orderDetails'. What you could do is add the start and end word anchors, eg 

^[A-Z]{1}[a-z]+[A-Z]?[a-z]*$

but as described, this won't work with Like and anyway and I'm not convinced it's worthwhile. All you really want to do (apart from the dbo schema check, which is easy and worthwhile), is check the first character is upper case. So in theory you could do something like this with the extremely powerful PBM ExecuteSql function:

ExecuteSql('Bool', 'SELECT CASE WHEN UPPER( LEFT( @@ObjectName, 1 ) ) = LEFT( @@ObjectName, 1 ) COLLATE SQL_Latin1_General_CP1_CS_AS THEN 1 ELSE 0 END')

I think I got this to work in the short time I spent on this and besides, you should probably be using camelCase : )

Another interesting example: https://www.simple-talk.com/blogs/2013/09/16/sql-server-policy-based-management-creating-a-custom-condition/

HTH

Policy Evaluation Results

Related Question