How To Check Stored Procedure Names Using Policy Based Management(RegEx)

Policy-Based Management is a feature that is introduced with SQL Server 2008. It allows us to set the rules we want in our systems, to set our standards and to make our controls automatically. For example, we can create a policy for control purposes so that the names of our stored procedures do not start with a number or database recovery models are not simple.

To comprehend Policy Based Management (PBM), you need to understand some concepts.

Facet

A feature that can be managed by PBM. For example, there is a facet called Stored Procedure. And you can use this facet to create a policy that checks characters in stored procedure names.

You can access all facets via SSMS as follows. To access the facet details, you must double-click on it.

Condition

Checks whether the sub-property of the related facets provide the specified condition. Örneğin Stored Procedure facet’ının Name alt özelliğinde istemediğiniz bir karakter olup olmadığını kontrol edebilirsiniz.

Let’s create a policy to check whether there are characters we don’t want in Stored Procedure names.

Create a Policy

Right click Policies tab as follows from the Management> Policy tab and click New Policy.

We give a name to Policy on the screen that appears. We need to create a condition from Check Condition.

Create a Condition

We’re clicking on New Condition.

We give a name to the condition from the screen that appears.

Because we will check the Stored Procedure names , we select the Stored Procedure facet from the Facet section.

By clicking “…” in the Expression section, we select @Name, which is the sub property of the Stored Procedure facet.

There are 4 parameters to check. Let’s explain them one by one.

The explanation of the first parameter -> ‘%[^A-Za-z0-9[_]]%’:

Explanation of % sign:

“the characters between this mark are used anywhere on the name of the stored procedure?”

Explanation of the ^ sign:

We use this sign to check if there are any characters other than the characters we specified after then this sign.

Explanation of A-Z:

This means letters A to Z. It refers to uppercase letters.

Explanation of a-z:

This means letters a to z. It refers to lowercase letters.

Explanation of [_]:

[_] sign means “_”.

Explanation of the second parameter-> ‘[0-9]%’

You have noticed that we do not put the % sign at the beginning of this parameter. So, stored procedure names must not start with a number. If stored procedure names start with a number, the policy will fail.

Explanation of the third parameter-> ‘[sp]%’

This is the same with the second parameter. That is, stored procedure names must not start with “sp”. Otherwise the policy will fail.

Explanation of the fourth parameter

In the fourth parameter we mean to not check the system stored procedures.

Explanation of all parameters

So with these parameters, we mean:

Stored Procedure can contain uppercase letters from A to Z, lowercase letters from a to z, numbers from 0 to 9, and “_”. If there is any characters other than these, the policy will fail.

Condition should be as follows

By selecting On Schedule from the Evaluation Mode, we select the interval at which the policy will be checked and then click the Enable check box.

If we choose On Demand, it only checks when we execute policy.

If you have a server-based condition, you can create a server-based condition from the server restriction section.

If you want it to be checked automatically at certain intervals, click New in the Schedule section and determine the frequency that the policy will check the condition. We set it up to work once every day at 12: 00: 000 PM.

We created our policy. We click Evaluate as below to run it manually.

I received a result as follows in my local.

When we click on View, we can see why the policy is failed.

When I clicked on the View, as you can see below, this stored procedure failed because it started with sp.

When you refresh the instance, a red color x sign appears on the left side of the instance, that looks like the letter. The reason for this is that we have a policy that is failed on the instance. When you see this mark, you must look at the defined policies and fulfill their requirements.

After correcting the Stored procedure names, this mark will disappear when we re-evaluate the policy as described above.

With Policy Based Management, you can check many things on SQL Server. And I think a professional database manager should use this feature that SQL Server offers us. You can find more articles on Policy Based Management at the following links.

“How To Check the Compatibility Level of Databases Using Policy Based Management“,

“How To Check Recovery Model of All Databases Using Policy Based Management“,

“How To Check VLF Counts in the Databases Using Policy Based Management”,

“How To Check SQL Logins That Password Policy Enforced or Password Expiration Enabled Using Policy Based Management”,

“How To Check Disabled Audits Using Policy Based Management”,

“How To Check Auto Shrink Option of Databases Using Policy Based Management”,

“How To Check Whether Availability Groups is Ready To Failover Using Policy Based Management”,

“How To Check Availability Group’s Backup Preference Using Policy Based Management”,

“How To Check Availability Group Automatic Failover Settings Using Policy Based Management”,

“How To Check Whether Readable Secondary is Enabled on Availability Groups Using Policy Based Management”,

“How To Check Whether Data File Sizes is Reached a Specific Size Using Policy Based Management”,

“How To Check Auto Close Option of Databases Using Policy Based Management”,

“How To Check Last Log Backup Time Using Policy Based Management”,

“How To Check Page Verify Option of Databases Using Policy Based Management”