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

I usually use a restored version of the production database as having a linked server to prod aint right on a dev box


-- 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]