Compare stored procedures, views and functions between two databases

After a release, a hotfix or a restore from backup, the question is often the same: which stored procedures are not what they should be? A procedure that exists in development but not in production is easy to spot. One that exists in both but with a different body is not. This article shows a query that lists both kinds for procedures, views, functions and triggers, and then the comparison with a side-by-side diff.

A T-SQL query for two databases on the same instance

Every procedure, view, function and trigger has its source text in sys.sql_modules. Comparing the text of the same object in two databases shows what differs:

SELECT COALESCE(s.object_name, t.object_name) AS object_name,
       COALESCE(s.type_desc, t.type_desc) AS type_desc,
       CASE WHEN t.object_name IS NULL THEN 'only in source'
            WHEN s.object_name IS NULL THEN 'only in target'
            ELSE 'different' END AS difference
FROM (SELECT QUOTENAME(sc.name) + '.' + QUOTENAME(o.name) AS object_name, o.type_desc,
             REPLACE(m.definition, CHAR(13), '') AS definition
      FROM DevDb.sys.sql_modules AS m
      JOIN DevDb.sys.objects AS o ON o.object_id = m.object_id
      JOIN DevDb.sys.schemas AS sc ON sc.schema_id = o.schema_id
      WHERE o.is_ms_shipped = 0) AS s
FULL OUTER JOIN
     (SELECT QUOTENAME(sc.name) + '.' + QUOTENAME(o.name) AS object_name, o.type_desc,
             REPLACE(m.definition, CHAR(13), '') AS definition
      FROM TestDb.sys.sql_modules AS m
      JOIN TestDb.sys.objects AS o ON o.object_id = m.object_id
      JOIN TestDb.sys.schemas AS sc ON sc.schema_id = o.schema_id
      WHERE o.is_ms_shipped = 0) AS t
  ON t.object_name = s.object_name AND t.type_desc = s.type_desc
WHERE s.object_name IS NULL OR t.object_name IS NULL OR s.definition <> t.definition
ORDER BY object_name;

Replace DevDb (the source) and TestDb (the target) with your database names. The result has one row per object that is not the same:

object_name	type_desc	difference
[dbo].[fnFormatName]	SQL_SCALAR_FUNCTION	different
[dbo].[uspNewProcedure]	SQL_STORED_PROCEDURE	only in source
[dbo].[uspOldProcedure]	SQL_STORED_PROCEDURE	only in target
[sales].[vCustomerOrders]	VIEW	different

Two details in the query matter. The schema names are read from sys.schemas of each database: SCHEMA_NAME() would look them up in the database you are connected to and return NULL for the other one. And CHAR(13) is removed because a script that was saved with Windows line endings in one database and Unix line endings in the other is not a real difference.

What the query does not catch

• Cosmetic changes count. Another comment, an extra space or a different case in a keyword makes the text differ, and the query cannot tell it from a real change. You have to open both versions to see which it is.
• Encrypted modules. A procedure created WITH ENCRYPTION has no text in sys.sql_modules: its definition is NULL, so a pair of them never shows up as different.
• Other databases and servers. The query needs both databases on one instance. Across servers you need a linked server, and a different collation can cause a collation conflict.
• Everything that is not a module. Tables, columns, indexes, constraints, permissions and extended properties are not in sys.sql_modules. How to compare two SQL Server databases has a query for objects and columns.
• The diff and the fix. The query names the objects. It does not show what changed inside them and does not make the script that brings the target in line.

With SQLTreeo Compare

SQLTreeo Compare reads both databases, on one server or on two, and lists every object in the groups Only in source, Different, Only in target and Identical. Select a procedure and the two versions are shown side by side with the changed lines marked. Filter the list by object type to see only the procedures, or only the views and functions.

1. Choose Compare database schemas on the start page.
2. Set the source and the target. A new comparison starts with the connections you used last.
3. Press Compare and filter the results by text or by object type.
4. Select an object to see its script diff. The deployment script that makes the target match the source can be copied or saved.

The same comparison covers the tables, indexes, constraints and permissions that the query above leaves out. Getting started with SQLTreeo Compare walks through a first comparison, and Detect schema drift in a CI pipeline runs it on every build.