A lot of teams set up naming standards or other conventions that they like to use in their code or with their database configurations. SQL Server has a policy-based management feature that will allow a DBA to set policies to use to evaluate code and/or configuration and catch any exceptions to the defined rules.
In my example, I wanted to set up a policy to find tables that have the prefix ‘tbl’. You could create a script to find these exceptions, but policy-based management gives a way to store this definition and run it for all databases.
SQL Server used the term facet for a database object, in this case a user defined table. The policy evaluates a condition(which is the rule we want to create to not allow the tbl table prefix) against a facet property, in this case the table Name.
Usually, I would prefer to use T-SQL to set this up. However in this case, creating the policies and conditions require calls to MSDB stored procedures, and with the condition, passing in XML with the rules we want to enforce. So it this case, using SSMS seems to be the best option.
In SSMS we go to the Management node, and then Policy Management. We can right-click on Policies and select ‘New Policy…’. We create a name for the policy, and then we can click on the Condition drop-down to create a new condition.
First we name our condition, and then choose a facet to run the condition against(In my example, Table). Then we build our rule. We’ll select ‘@Name’ for the Field(The property of the facet that we want to evaluate), for Operator we select ‘NOT IN’, and for Value ‘tbl%’. For the value, we can use the same wildcards that we would use in a T-SQL statement. Click ‘OK’ to create the condition.
This takes us back to the ‘Create New Policy’ dialog. For ‘Against Targets’, we’ll keep the defaults of every table in every database. If we wanted, we could create new conditions to only enforce the condition for certain tables and/or certain databases.
‘Evaluation Mode’ allows us to determine how the rules are enforced. In this case, we have two options: ‘On demand’ and ‘On schedule’. We’ll keep ‘On Demand’ and we’ll run the evaluation manually. ‘On Schedule’ would allow us to set up an agent job to run the evaluation. Click ‘OK’ to save the policy.
In certain situations, we would see two additional options: ‘On change: prevent’ and ‘On change: log only’. SQL Server Central has an article to explain when these additional options are available. SQL Shack also has an interesting article on the evaluation modes.
So now the policy has been created, so we can evaluate it. I’ve saved a SQL script to Github to create two tables, one with the tbl prefix and another without, so we can test the policy. The script also creates the policy and condition (this SQL is generated by SSMS after I manually created these objects). We can right-click on ‘Policies’ and then ‘Evaluate…’. We can select the policy we just created, and then click ‘Evaluate’. We should get one table that passes the check, and the one table named ‘tblTable’ should fail.
These policies give you a lot of power to enforce any sort of code conventions or database settings that you would like to keep an eye on.