Click here to Skip to main content
Click here to Skip to main content

Keep everyone away from breaking your database object naming convention with SQL Server Policy Based Management.

, 19 Feb 2013
Rate this:
Please Sign up or sign in to vote.
Standardization database object name with SQL Server Policy Based Management.

Introduction

Maintaining proper naming convention for database objects is very crucial for every database project. Think about a situation where 10-15 database developers work together.  If one of them creates a table with a name of Customers_tbl another one Tbl_Department or Salary, that could be quite  cumbersome for future maintenance and extensibility. Another issue commonly comes in case of creating stored procedure with a prefix "sp_". All of you know that creating objects with a prefix of "_sp" means a special objects for SQL Server and SQL Server always try to locate the object in master database first. So it is always recommended that object naming with a prefix of "_sp" should always be avoided. To get rid of this situation, it is very common practice now a days that every team has its own policy to maintain precise database object naming convention. Policy Based Management is one of the most splendid features of SQL server, that makes this task extremely handy for database developer or DBA as well.  

Define the policy first: 

Assume, we need some database object naming policies which will force database users to create the object names by obeying the defined naming policy. 

  •  No object name will be start with a prefix sp_. 
  • In terms of Table no prefix or suffix will exist with a value of "tbl".
  • Must have a suffix _usp (User Defined Stored Procedure) with every Stored procedures name. 
  • Must have a _udf suffix with every user defined function. 
  • Every view must have a suffix of  _vw. 

In this article we will not implement all of these policies, rather I will show you how to create the following policy using SQL Server Policy Based Management.

"All stored procedures in Database AdventureWorks2012 must not start with 'sp_' and must have a '_usp' end of (suffix) every stored procedure name."  

Implementation  

Creating Conditions to satisfy the Policy

Now, we will  try to figure out the conditions from our declared policy.

  • Condition 1: "All stored procedures of Database AdventureWorks2012" indicates that our policy will be applicable only for AdventureWorks2012 database.
  • Condition 2: "must not start with 'sp_' and must have a '_usp' end of each stored procedure name."  this part indicates, the stored procedures must not have a prefix value sp_ and must have suffix '_usp'. 

We We have our defined conditions, let's create them now step by step.

  1. Open SSMS select Management > Policy Management> Conditions 
  2. Right click on Conditions node and Select 'New Condition..' 
  3. Create new Condition window will appear  like below: 
Figure 1: Creating Condition 1 

Now to create Condition 1. put the  AdventureWorks2012 Database in the Name Field. Select Database from the Facet dropdown list. Finally, in the Expression Grid, put @Name in the Field cell and 'AdventureWorks2012'  in the Value cell. (Do not forget to put single quote (') in the value cell). Now press OK button. Expand the Conditions node and you will find the newly created condition now.  

Figure 2: Condition 1 Created 

To create  the second condition repeated step 2 and put the following values in the specific fields of the "Create New condition-" window.

Name= Stored Procedure Naming Condition.

Facet=Stored Procedure.

Under Expression Grid:

AndOr=Empty   Field=@Name   Operator=NOT LIKE        Value='sp_%'

AndOr=AND       Field=@Name   Operator= LIKE               Value='%_usp' 

Figure 3: Creating Condition 2.

Hit OK button to create this condition as well. Now we have our 2 conditions ready.

How it works

Selecting "Stored Procedure" from Faced indicates the expression  will be applicable for stored procedure only. And what configuration we have made in the expression section is just to comply with the second condition. The expression will be:

@Name(name of the stored procedure)  NOT LIKE 'sp_%'  AND @Name LIKE  '%_usp' 

Creating Policy

  1. From SSMS select Management > Policy Management> Policies
  2. Right click on Policies and hit on New Policy.. context menu.
  3. Enter Stored Procedure Naming Policy on the Name: field. At this moment you will get Enabled: check box in disabled mode just after the Name: field.
  4. Select Stored Procedure Naming Condition from Check condition: drop down.
  5. From the Against targets: (looks like below) 

Figure 4: Setting Targets while creating policy 

Click over the down arrow just before the Database, and select  AdventureWorks2012 Database condition from the context menu.

  1. Select  "On Change: prevent" from Evaluation Mode: drop down list.

  2. The Enabled: check box has become Enabled now, put a check mark on it. 

At this stage the entire window looks like: 

policy create

Figure 5: Creating Policy 
  1. Press OK to finish.  

We are done! Let's test our works now.  

  1. From  SSMS open a new query window and point out the database AdventureWorks2012 or Run following scripts: 
    USE AdventureWorks2012
    GO
  2. Let's try to create a Stored procedure with a sp_ prefix.

    First SP Attempt Fail

    Figure 6: SP Creation attempt fails with sp_ prefix 
  3. Again try to create the same procedure with a sp_ prefix and _usp suffix. 

    Figure 7: SP Creation attempt fails with sp_ prefix and _usp suffix.

    This time our attempt also fails.  

  4. Now let's try to create the Stored Procedure by fully honoring the policy such as not giving sp_ prefix and giving _usp suffix, see below: 

    Figure 8: SP Creation attempt successful.  

Hurray!!!  the procedure created successfully.

Now let's discuss about some terms and concepts of SQL Server  Policy Based Management. 

What is policy Based Management

Policy Based Management is a method of database administration which provides a management framework in order to automate tasks according to a set of predefined standard or policy. This standardization activities can be imposed across multiple sever.  

Policy-Based Management Scenarios

A very good MSDN article exists regarding this. Follow the link below: 

http://msdn.microsoft.com/en-us/library/bb522466(v=sql.105).aspx 

Policy Based Management Concepts and Terms

A details explanation  can be found in MSDN about this topic as well. Here you go:

http://msdn.microsoft.com/en-us/library/bb510667.aspx 

Conclusion

In term of managing security, complexity, configuration reliability and enforcing strict compliance policies across large distributed environments, undoubtedly, Policy Based Management offers wide range of facilities. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Khorshed Alam, Dhaka
Database Developer The Jaxara IT Ltd.
Bangladesh Bangladesh
I have 10.5 years of cumulative professional experience in information technology. Specifically, in areas of database administration, development, system appreciation, requirement analysis and web development.
 
Obtained following Microsoft Certifications:
 
•Microsoft Certified IT Professional (Database Developer 2008)
Microsoft Corporation, License D266-6721
•Microsoft Certified Technology Specialist ( SQL Server 2008, Database Development)
Microsoft Corporation, License C149-3291
•Microsoft Certified Technology Specialist (SQL Server 2008, Implementation and Maintenance)
Microsoft Corporation, License C149-3290
 
http://lnkd.in/s2DveK

Comments and Discussions

 
Question'sp_%' filter is incorrect PinmemberAmit_Bansal1-Dec-13 16:58 
AnswerRe: 'sp_%' filter is incorrect PinmemberKhorshed Alam, Dhaka16-Dec-13 21:51 
Questiontypo PinmemberSixLiteralDays19-Feb-13 8:38 
AnswerRe: typo PinmemberKhorshed Alam, Dhaka19-Feb-13 15:42 
GeneralMy vote of 5 PinmemberS. M. Ahasan Habib15-Feb-13 6:07 
GeneralMy vote of 5 PinmemberVC Sekhar Parepalli15-Feb-13 4:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 20 Feb 2013
Article Copyright 2013 by Khorshed Alam, Dhaka
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid