Issue with Table Naming Conventions and Policy Management in SQL Server 2016

In SQL Server 2012, I had a policy set to not allow spaces in a table name. However, when I use the same policy in SQL Server 2016, I get an error.

Here is the code for the condition:

DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'No Spaces', @description=N'No spaces in table names.', @facet=N'IMultipartNameFacet', @expression=N'
  Bool
  NOT_LIKE
  2
  
    String
    Name
  
  
    String
    System.String
    % %
  
', @is_name_condition=4, @obj_name=N'% %', @condition_id=@condition_id OUTPUT
SELECT @condition_id

Here is the code for the policy:

```
DECLARE @object_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Table Names_ObjectSet', @facet=N'IMultipartNameFacet', @object_set_id=@object_set_id OUTPUT
SELECT @object_set_id

DECLARE @target_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/Sequence', @type=N'SEQUENCE', @enabled=False, @target_set_id=@target_set_id OUTPUT
SELECT @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Sequence', @level_name=N'Sequence', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @enabled=False, @target_set_id=@target_set_id OUTPUT
SELECT @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/StoredProcedure', @level_name=N'StoredProced

Tested the scripts on a SQL Server 2016 SP1 CU2 instance and the policy works if the Evaluation mode is set to "On Change : Prevent". (there is a bug that doesn't let you evaluate policies that use specific facets).

Meanwhile, if you only use the policy for table names, you can also try the "Table Option" facet instead of the "MultipartName", with the same configuration (@NAME NOT LIKE '% %').

StackExchange Database Administrators Q#172292, answer score: 7