Comparing Stored-Procedures

Had a bit of a problem today with the re-write project.

I had been checking new stored-procedures in the DEV database, and (if good) pasting them into the WEB database.

The issue was that some DEV stored-procedures that I had already checked-in to WEB had been modified again.

Rather than trying to enforce version-control (mmm), or download Redgate’s SQL Compare, I modified my ‘Whats New” routine to compare the modify-dates between the DEV and WEB databases.

-- CompareSP.sql

SELECT [dev].[type_desc],
       (SELECT [name] FROM [companydev].[sys].[schemas] WHERE [schema_id] = [dev].[schema_id]) [schema],
       CASE [dev].[parent_object_id]
           WHEN '0' THEN [dev].[name]
           ELSE OBJECT_NAME([dev].[parent_object_id]) + '.' + [dev].[name]
       END [object_name],
       [dev].[create_date],
       [dev].[modify_date], -- or create-date if there isn't one
	   '' v,
	   [web].[modify_date] web_modify_date , 
	   DATEDIFF(MINUTE, [dev].[modify_date], [web].[modify_date]) mod_diff
FROM [companydev].[sys].[objects] dev
JOIN [companyweb].[sys].[objects] web
  ON [dev].[name] = [web].[name]
WHERE [dev].[is_ms_shipped] = 0 -- exclude system-objects
AND [dev].[type] = 'P' -- just stored-procedures
--AND [dev].[modify_date] > '21 nov 2018'
ORDER BY [dev].[modify_date] DESC;