Today, I’ll show you how to create a Naming convention Policy specifically checking for the “Any User Defined Procedure name should start with 'P_[A-Z][A-Z][A-Z]_%" policy. If someone doesn’t follow this naming convention style then he won’t be able to create procedure.
We can Execute this Policy for SQL Server 2000/2005/2008 objects(SPs)
Any User Defined Procedure name should start with 'P_[A-Z][A-Z][A-Z]_%’
Ø Policy Description
commonly organizations define this kind of policy which force users to create objects names that should follow certain pattern. I have seen some industries have standards like name should start with usp_%. You can choose your industry's standard.
Basically this Policy will act like a Before Insert Trigger and It will check weather the name of the object follows naming conventions or not.
Policy Creation:-
Step 1 – Create a condition to check the name of the object being created.
Name: Sp_Naming_Conventions
Facet: MultiPart Name
Expression: @Name LIKE 'p[_][a-z][a-z][a-z][_]%'
Step 2 – Create a condition to check the name of the object being created.
Name: Filter_UserDatabase_Condition
Facet: Database
Expression: @ID > 4
Step 3 --Create a policy that checks for “Any User Defined Procedure name should start with 'P_[A-Z][A-Z][A-Z]_%.” This will use the condition that we’ve created above.
Below is the policy details.
General Tab
Name: SP_Naming_Conventions
Check Condition:
Against Target: Every Stored Procedures in Every Database.
Evaluation Mode: On change: Prevent
Server Restriction: None
Description Tab
Category: Object_Naming_Conventions (you can create a new category and give it any name)
Description: This Policy will check naming conventions for all SP in all user databases.All Sp Names should satisfy this criteria - ''p_abc_%''
Where 1)name of sp should start with p =position 1
2)position 2 and 6 must be underscores
3)position 3,4 and 5 must be alphabets
When Enabled,It will prevent any user from creating a SP that doesnt follow the criteria above... (this message will display if the policy is violated)
Step 4 – Now we need to execute this policy against any of the user SQL Server Dataabse.
Please feel free to email me with any questions/comments you might have related to this topic.My email is nparikh@iie.org
Nirav Parikh
Database Administrator
