I spent a couple hours this morning preparing a turnover for a client, and I needed to make sure the SQL I was moving to production only had the changes I was expecting. The environment I inherited did not source control the stored procedures, so I needed a way to diff the production and staging stored procedures to make sure I wasn't introducing any code a previous developer had left lying around on the staging site.

I decided to break the problem into 3 parts:

1. Find any SPs on the two databases that are different (or exist only in the staging db)
2. Write them to their own folders on the server
3. Call my trusty old copy of WinDiff on the two folders and compare the differences

So, first, how do you determine which stored procedures have changed? I found a reference to the INFORMATION_SCHEMA on google, which has the ROUTINE_DEFINITION stored in it. I wrote the following:

CREATE PROCEDURE spDiffSps
     @cutoffDate DateTime = NULL
AS
BEGIN
    SELECT STAG.ROUTINE_NAME,
            STAG.CREATED AS staging_created,
            STAG.LAST_ALTERED AS staging_last_altered,
            STAG.ROUTINE_DEFINITION AS staging_routine,
            PROD.CREATED AS production_created,
            PROD.LAST_ALTERED AS production_last_altered,
            PROD.ROUTINE_DEFINITION AS production_routine
    FROM INFORMATION_SCHEMA.ROUTINES STAG
    LEFT OUTER JOIN production.INFORMATION_SCHEMA.ROUTINES PROD ON STAG.ROUTINE_NAME=PROD.ROUTINE_NAME
    WHERE STAG.ROUTINE_TYPE = 'PROCEDURE' AND
            (PROD.ROUTINE_DEFINITION IS NULL OR STAG.ROUTINE_DEFINITION<>PROD.ROUTINE_DEFINITION)
            AND STAG.LAST_ALTERED > IsNull(@cutoffDate, '1/1/2000')
    ORDER BY STAG.LAST_ALTERED DESC
END

This will return any SPs where the actual text is different between the two databases, after a certain cutoff. I added the cutoff, as I am only interested in the SPs I have modified since a certain date. I placed this in a stored procedure in the staging database.

Next, we call this SP from any of our favorite languages.  Since the client I am working for is a VB shop, I just wrote the files out from VB to a couple of folders on the web server by calling this SP above from a aspx.vb web page::

   Dim a_row As DataRow
   For Each a_row In dt.Rows
       Dim a_fileName As String = "C:\\TEMP\\STAG\\" + a_row.Item("ROUTINE_NAME").ToString()
       File.WriteAllText(a_fileName, a_row.Item("staging_routine").ToString())
       Dim fileInfo As FileInfo = New FileInfo(a_fileName)
       fileInfo.CreationTime = DateTime.Parse(a_row.Item("staging_created").ToString())
       fileInfo.LastWriteTime = DateTime.Parse(a_row.Item("staging_last_altered").ToString()) 

       If (Not DBNull.Value.Equals(a_row.Item("production_routine"))) Then
          a_fileName = "C:\\TEMP\\PROD\\" + a_row.Item("ROUTINE_NAME").ToString()
          File.WriteAllText(a_fileName, a_row.Item("production_routine").ToString())
          fileInfo = New FileInfo(a_fileName)
          fileInfo.CreationTime = DateTime.Parse(a_row.Item("production_created").ToString())
          fileInfo.LastWriteTime = DateTime.Parse(a_row.Item("production_last_altered").ToString())
       End If
   Next

I set the LastWriteTime in case you want to sort in Explorer on the date.  Now that we have the source for both versions in two directories, just load up WinDiff, and pick the two directories.

Of course, this being computer software, something just doesn't quite work out.  This time, that something is that the wonderful INFORMATION_SCHEMA.ROUTINE_DEFINITION is actually only defined as a nvarchar(4000), so we end up getting a truncated version of the routine if it is longer than 4000 bytes.  What we need to do is get the full definition from either sys.objects or sys.procedures.  To do that, you have to call object_definition on the procedure's object id.  This works fine to get the object definition in the database your SP is running in, but to get the definition in the production database, you will need to write a function dedicated to this over there, since you can't reference OBJECT_DEFINITION in another DB.

CREATE FUNCTION object_definition_local
(
      @p1 int
)
RETURNS nvarchar(max)
AS
BEGIN
      DECLARE @Result varchar(max)
      SELECT @Result = OBJECT_DEFINITION(@p1)
      RETURN @Result
END

Now, with this local call to object_definition on the production database, you can finally get all the SP text in the file:

SELECT STAG.name AS ROUTINE_NAME,
      STAG.create_date AS staging_created,
      STAG.modify_date AS staging_last_altered,
      object_definition(STAG.object_id) AS staging_routine,
      PROD.create_date AS production_created,
      PROD.modify_date AS production_last_altered,
      production.dbo.OBJECT_DEFINITION_LOCAL(PROD.object_id) AS production_routine
FROM sys.procedures STAG
LEFT OUTER JOIN production.sys.procedures PROD ON STAG.name=PROD.name
WHERE (PROD.modify_date IS NULL OR object_definition(STAG.object_id)<>production.dbo.OBJECT_DEFINITION_LOCAL(PROD.object_id))
      AND STAG.modify_date > IsNull(@cutoffDate, '1/1/2000')
ORDER BY STAG.modify_date DESC