Creating Custom Conditions In SQL Server Policy Based Management

In my last post I covered some basic policy based management examples, in this post I want to cover writing custom conditions in TSQL.

Let’s image we want a policy that makes sure every table in our database has a RowVersion type field in it. As in the previous post you would first create a condition to pick the databases for the policy to run on. Then we need a condition that will fail if any table doesn’t have a RowVersion field, as there are no facets built in for this we’ll have to write a custom one.

The TSQL to do this for a given table looks like this

IF EXISTS(
      SELECT *
      FROM sys.columns
      WHERE
         Object_id = OBJECT_ID('MyTableWithRowVersion')
         AND system_type_id = 189
)
   SELECT 1
ELSE
   SELECT 0

We can replace the hard coded table name in the above example with @@ObjectName and the policy will replace that when it runs with the name of each object that it tests against.

IF EXISTS(
      SELECT *
      FROM sys.columns
      WHERE
         Object_id = OBJECT_ID(@@ObjectName)
         AND system_type_id = 189
)
   SELECT 1
ELSE
   SELECT 0

To use this we’ll create a new condition with a facet of table and instead of picking a field click the ellipsis button to the right of it so we can enter out custom SQL. You need to use the ExecuteSql function and tell it we’re returning a number…

ExecuteSql('Numeric', '
IF EXISTS(
      SELECT *
      FROM sys.columns
      WHERE
         Object_id = OBJECT_ID(@@ObjectName)
         AND system_type_id = 189
)
   SELECT 1
ELSE
   SELECT 0'
)

We can then finish the condition by putting 0 into the value so it will error for anything that returns 1 (No RowVersion found).

You can then create a policy that targets your database and uses the condition above to check for the existence of ROWVERSION.