#010: Comparing Stored Procs in different databases

Sometimes it’s a pain with a Dev database and a Prod database to see where it is changed for migrating stored procs.

This can be quite slow, but really useful


-- Needs table variables because the information_schema results don't compare nicely. Weird.

DECLARE @DevDB TABLE ([SCHEMA] VARCHAR(255), [NAME] VARCHAR(255), [TYPE] VARCHAR(255), [DEFINITION] VARCHAR(8000))

INSERT INTO @DevDB
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
FROM DevDB.INFORMATION_SCHEMA.ROUTINES

DECLARE @ProdDB TABLE ([SCHEMA] VARCHAR(255), [NAME] VARCHAR(255), [TYPE] VARCHAR(255), [DEFINITION] VARCHAR(8000))

INSERT INTO @ProdDB
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
FROM ProdDB.INFORMATION_SCHEMA.ROUTINES

-----------------------

SELECT 'DevDB' AS tblName, *
FROM (
	SELECT * FROM @DevDB	
	EXCEPT	
	SELECT * FROM @ProdDB
	) X

UNION ALL

SELECT 'ProdDB' AS tblName, *
FROM (
	SELECT * FROM @ProdDB	
	EXCEPT	
	SELECT * FROM @DevDB
	) X
ORDER BY [TYPE], NAME, [SCHEMA], [tblName]
Written on May 9, 2018