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).