This question came up recently so I decided to blog about it

If you have your stored procedures in source control and you want to see if they are the same as the one on the server, all you need to do is script out the procs and do a diff right?

Let’s see what happens, create this proc

Create procedure prTest
as
set nocount on
if exists (select 1 from sys.procedures where name = 'prTest')
print 'yes'
else
print'no'

Now script it out, you do this by finding the proc under your database–>programmability–>stored procedures. Right click on the proc, select script procedure as and then choose create to and pick a new window

Your procedure will look like this

/****** Object:StoredProcedure [dbo].[prTest] Script Date: 04/28/2009 10:07:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[prTest]
as
set nocount on
if exists (select 1 from sys.procedures where name = 'prTest')
print 'yes'
else
print'no'

Do you see the problem? The first line has a comment and this will make your proc unique every time you script it out. Fortunately you can disable this by unchecking include descriptive headers. The process to do this is different for SQL Server 2000, 2005 and 2008

For SQL Server 2000 it is under the formatting tab in the Generate SQL Script object (see image below)

For SQL Server 2005 it is under Tools–>Options–>Scripting(see image below)

For SQL Server 2008 it is under Tools–>Options–>SQL Server Object Explorer–>Scripting(see image below)