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 –>–>. Right click on the proc, select and then choose and pick
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 . 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)