Database compatibility level check with Policy Based Management

Sometime database compatibility level is lower than server level. Normally database compatibility level must be up to date. If you have lots of SQL Servers, to check all databases manually is not  the optimum way.
You can create a policy and check this all servers by the policy.
But to create a policy for this purpose is not so easy because you must create policy for all major versions. I've created a policy checks all SQL Servers that has version 2005 or up version.

Here is the code, please run accordingly.


Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Server which is 2005 or up', @description=N'', @facet=N'Server', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>GE</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>Numeric</TypeClass>
    <Name>VersionMajor</Name>
  </Attribute>
  <Constant>
    <TypeClass>Numeric</TypeClass>
    <ObjType>System.Double</ObjType>
    <Value>9</Value>
  </Constant>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Database compatibility level must be equal to server level', @description=N'', @facet=N'Database', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>EQ</OpType>
  <Count>2</Count>
  <Function>
    <TypeClass>String</TypeClass>
    <FunctionType>String</FunctionType>
    <ReturnType>String</ReturnType>
    <Count>1</Count>
    <Attribute>
      <TypeClass>Numeric</TypeClass>
      <Name>CompatibilityLevel</Name>
    </Attribute>
  </Function>
  <Function>
    <TypeClass>String</TypeClass>
    <FunctionType>ExecuteSql</FunctionType>
    <ReturnType>String</ReturnType>
    <Count>2</Count>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>String</Value>
    </Constant>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>select  ''''Version''''+convert(varchar,compatibility_level) from sys.databases where name = ''''master'''' </Value>
    </Constant>
  </Function>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Database compatibility level must not be lower than server level_ObjectSet', @facet=N'Database', @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'Database compatibility level must not be lower than server level_ObjectSet', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @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

GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Database compatibility level must not be lower than server level', @condition_name=N'Database compatibility level must be equal to server level', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'Server which is 2005 or up', @object_set=N'Database compatibility level must not be lower than server level_ObjectSet'
Select @policy_id

GO