As I'm sure many of you know, peer pressure can be a very powerful motivator... and I am not immune to such pressure. So, hot on the heels of Phil and Jon's posts on the magic voodoo that can be accomplished with the INFORMATION_SCHEMA Views, I felt obligated to add my own small bit of wisdom to the fray.

In reading through Phil's article I noticed he didn't mention anything in particular about the creating, modifying, and/or dropping of Stored Procedures when working with a common schema among a development team. This is one thing we often have to deal with on the subText team... so what better opportunity to share than right now!

Keeping those Stored Procs in Check

In the following example we want to check for the existence of the subtext_ClearBlogContent stored procedure in the dbo schema. If we find said stored procedure, we want to drop it so that later we can create a new one (that may or may not have been changed since the last time we built our database). The script to do so is as follows:

if exists 
  (select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES 
      where ROUTINE_TYPE = 'PROCEDURE' 
      and OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMsShipped') = 0 
      and ROUTINE_SCHEMA = 'dbo' 
      and ROUTINE_NAME = 'subtext_ClearBlogContent')
drop procedure [dbo].[subtext_ClearBlogContent] 
GO

The important things to note here are that (1) we only want to look for (and possibly drop) procedures that did not come installed with SQL Server, and (2) we are looking in a particular schema (the dbo schema) for the stored procedure. We can easily make this script more flexible by adding a couple of template parameters like so:

if exists 
  (select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES 
      where ROUTINE_TYPE = 'PROCEDURE' 
      and OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMsShipped') = 0 
      and ROUTINE_SCHEMA = '<dbUser,varchar,dbo>' 
      and ROUTINE_NAME = 'subtext_ClearBlogContent')
drop procedure [<dbUser,varchar,dbo>].[subtext_ClearBlogContent] 
GO

Notice the two lined where I've bolded <dbUser,varchar,dbo>. This would allow us to define the particular schema at run time. Don't forget, that to run the second example in Query Analyzer, you'll need to hit CTRL+SHIFT+M.

UPDATE: I had a slight typo in the original example scripts where I had square brackets around the variable in the and ROUTINE_SCHEMA = 'dbo' lines. The are fixed and working beautifully now!