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

Question

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?

Answer 1

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

Answer 2

I think you mean PascalCase (not camelCase) since Title Case only applies when there is white space separating the words.

I agree with @wBob that the best you can do via T-SQL is ensure that the first letter is upper-case. Though you could probably go one step further, extending the condition suggested by @wBob, and ensure that the 2nd letter (assuming it wouldn't be accepted to have a single letter table name anyway) is lower-case by following the same pattern (and you might as well use a binary Collation since you are just comparing the character to itself):

ExecuteSql('Bool',
           'SELECT CASE
                     WHEN  UPPER( LEFT( @@ObjectName, 1 ) ) =
                           LEFT( @@ObjectName, 1 ) COLLATE Latin1_General_100_BIN2
                       AND LOWER( SUBSTRING( @@ObjectName, 2, 1 ) ) =
                           SUBSTRING( @@ObjectName, 2, 1 ) COLLATE Latin1_General_100_BIN2
                       THEN 1 ELSE 0 END')

However, as long as you are doing ExecuteSql, you could probably go another step towards the goal and apply actual Regular Expressions. RegEx functionality can only be attained via SQLCLR, in which case you can code your own or download the Free version of SQL# (which I created, but again, the RegEx functions are free), and use a pattern such as: \b(?:\p{Lu}\p{Ll}+)+\b. What this pattern is doing is:

• \b means "word boundary". It won't match in the middle of a word, but will match before or after word characters when the adjacent character is white space, punctuation, etc.
• (?: ... )+ means a non-capturing group that is found 1 or more times.
• \p{Lu} means any single character matching the Unicode category of "Upper-case Letter". The \p{...} means "Unicode Category" while the Lu means the "Upper-case Letter" category specifically (and L by itself would mean any letter, regardless of case). That allows this condition to accept upper-case letters from other languages that do not fall into the A-Z range. There is no need to specify {1} to denote a single instance since that is the default anyway.
• \p{Ll}+ means 1 or more characters matching the Unicode category of "Lower-case Letter".

While I believe this pattern captures the intent of the pattern you were attempting, as you can see from the results below, it still does not handle cases when there should be a capital used in the middle of the name but it is currently lower-case:

DECLARE @Tests TABLE (Name NVARCHAR(50), ExpectedResult BIT);
INSERT INTO @Tests (Name, ExpectedResult) VALUES (N'OrderDetail', 1);
INSERT INTO @Tests (Name, ExpectedResult) VALUES (N'orderDetail', 0);
INSERT INTO @Tests (Name, ExpectedResult) VALUES (N'Orderdetail', 0);
INSERT INTO @Tests (Name, ExpectedResult) VALUES (N'OrderDEtail', 0);
INSERT INTO @Tests (Name, ExpectedResult) VALUES (N'ORderDetail', 0);
INSERT INTO @Tests (Name, ExpectedResult) VALUES (N'OrderDetaiL', 0);

SELECT t.[Name],
       t.ExpectedResult,
       SQL#.RegEx_IsMatch(t.[Name], N'\b(?:\p{Lu}\p{Ll}+)+\b', 1, NULL) AS [ActualResult]
FROM   @Tests t

Returns:

Name           ExpectedResult    ActualResult
OrderDetail    1                 1
orderDetail    0                 0
Orderdetail    0                 1
OrderDEtail    0                 0
ORderDetail    0                 0
OrderDetaiL    0                 0 

Only a human can determine that Orderdetail should probably be OrderDetail. And this is why, regardless of how you implement the Condition, the "Evaluation Mode" of the Policy should not be "On change: prevent". This type of standard / policy really should be left to code reviews, so best to leave the "Evaluation Mode" as "On demand" and perhaps run it at the end of each development cycle (while there is still an opportunity to fix it before any violation goes to Production).