Policy Management Enforcing naming convention in Database(s) in SQL Server 2008

Yesterday I was reviewing one of my latest projects and I found that one of the developers had created two stored procedure with “SP_” prefix.  Generally “SP_” prefix is used for System stored procedure name; look at one of my past article how to create your own system SP,  if You will create your own stored procedure with “SP_”, It will not give you any error, it will get executed successfully but it is really a overhead on processing as whenever compiler will get prefix “SP_” it will going to look into system SPs list, once it won’t get it from there, will look at user SP list. Apart from this, there should be some naming convention rules for user defined object that is what I believe so I thought to put some policy enforcement so that, even by mistake, nobody can do it.

Since this is not a tutorial article for Policy Management, I will not going into deep dive in Facets, Condition and policy, rather I will use it to show you one practical example.

1.)    Create your condition by right clicking on “Condition” tab under Instance Name-> Management-> Policy Management-> Conditions-> New Conditions

2.)    In a conditions dialog box, give Name as a “SPNamingConventions”, select facet “Multipart Name” and in expression give @Name NOT LIKE ‘SP_%’

3.)    After clicking on “OK” button in above dialog box, right click on “Plolicies”, select “New Policy” and fill out details as mentioned in below screen capture.

Finally you are giving Policy Name “Stored Procedure Naming Convention Policy”, making it enable, which is MUST, by check box given. Select the condition you created in step # 2 and also selecting the target from given list. Once you done with that, don’t forget to select “On Change: Prevent” Evaluation Mode and also don’t forget to click on “OK” button

Once you are done with that, try to create stored procedure with prefix “SP_”. I am doing it in my server in AdventureWorks database; however you can try it in your own database.

Use AdventureWorks

GO

Create PROC SP_TestProc

AS

BEGIN

      SELECT 'Ritesh' as Name,'SQLHub.com' as Website

END

--You will be greeted with error something like below

--Policy 'Stored Procedure Naming Convention Policy' has been violated by

--'SQLSERVER:\SQL\RITESH-SRV\SQL2K8\Databases\AdventureWorks\StoredProcedures\dbo.SP_TestProc'.

--This transaction will be rolled back.

--Policy condition: '@Name NOT LIKE 'sp_%''

--Policy description: ''

--Additional help: '' : ''

--Statement: '

--Create PROC SP_TestProc

--AS

--BEGIN

--    SELECT 'Ritesh' as Name,'SQLHub.com' as Website

--END

--'.

--Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65

--The transaction ended in the trigger. The batch has been aborted.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com