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