There are plenty of tools to compare database schemas. But why using them while SQL Server provides everything for you through System Views (check my blog How to check the schema of your Database through queries)? This blog shows you how to do that.

In this blog, we will refer to the source database as "Source", and the destination database as "Destination". They are considered in the examples as on the same server.

For each comparison, we will be checking:

  1. The new objects
  2. The objects that need to be deleted (not present in the Source database but present in the Destination database)
  3. The modified objects

First of all, we will compare the objects in the databases in order to find out what are the new objects and the objects that need to be deleted. To do that, we will use the sys.objects system view. It shows all the objects in the database, along with their object_id which is used to identify the objects in other system views:

-- Objects to be created 

    SELECT name, type_desc

    FROM Source.sys.objects

    WHERE name NOT IN (SELECT name FROM Destination.sys.objects)

 -- Objects to be deleted 

    SELECT name, type_desc

    FROM Destination.sys.objects

    WHERE name NOT IN (SELECT name FROM Source.sys.objects)

Next, we will check all the columns to see the new columns to be added, the old ones to be deleted, and the modified ones. We will use the INFORMATION_SCHEMA.COLUMNS system view:

-- Columns to be created

SELECT TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME,

DATA_TYPE + ISNULL('(' + (CASE WHEN CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) END ) + ')' , ISNULL('(' + CONVERT(varchar, NUMERIC_PRECISION) + ', ' + CONVERT(varchar, NUMERIC_SCALE) + ')', '')) + (CASE WHEN IS_NULLABLE = 'YES' THEN ', null' ELSE ', not null' END)

FROM Source.INFORMATION_SCHEMA.COLUMNS source

WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Destination.INFORMATION_SCHEMA.COLUMNS destination

WHERE source.TABLE_NAME = destination.TABLE_NAME)

AND TABLE_NAME IN (SELECT TABLE_NAME

FROM Destination.INFORMATION_SCHEMA.TABLES)

-- Columns to be removed

SELECT TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME,

DATA_TYPE + ISNULL('(' + (CASE WHEN CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) END ) + ')' , ISNULL('(' + CONVERT(varchar, NUMERIC_PRECISION) + ', ' + CONVERT(varchar, NUMERIC_SCALE) + ')', '')) + (CASE WHEN IS_NULLABLE = 'YES' THEN ', null' ELSE ', not null' END)

FROM Destination.INFORMATION_SCHEMA.COLUMNS destination

WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Source.INFORMATION_SCHEMA.COLUMNS source

WHERE source.TABLE_NAME = destination.TABLE_NAME)

AND TABLE_NAME IN (SELECT TABLE_NAME

FROM Source.INFORMATION_SCHEMA.TABLES)

-- Columns to be modified

SELECT source.TABLE_SCHEMA + '.' + source.TABLE_NAME + '.' + source.COLUMN_NAME,

source.DATA_TYPE + ISNULL('(' + (CASE WHEN CONVERT(varchar, source.CHARACTER_MAXIMUM_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(varchar, source.CHARACTER_MAXIMUM_LENGTH) END ) + ')' , ISNULL('(' + CONVERT(varchar, source.NUMERIC_PRECISION) + ', ' + CONVERT(varchar, source.NUMERIC_SCALE) + ')', '')) + (CASE WHEN source.IS_NULLABLE = 'YES' THEN ', null' ELSE ', not null' END),

FROM Source.INFORMATION_SCHEMA.COLUMNS source

INNER JOIN Destination.INFORMATION_SCHEMA.COLUMNS destination

ON source.TABLE_NAME = destination.TABLE_NAME

AND source.COLUMN_NAME = destination.COLUMN_NAME

WHERE source.COLUMN_DEFAULT <> destination.COLUMN_DEFAULT

OR source.DATA_TYPE <> destination.DATA_TYPE

OR source.CHARACTER_MAXIMUM_LENGTH <> destination.CHARACTER_MAXIMUM_LENGTH OR source.NUMERIC_PRECISION <> destination.NUMERIC_PRECISION

OR source.NUMERIC_SCALE <> destination.NUMERIC_SCALE

The most difficult part is to find out the updated stored procedures, user defined functions, and views. We will use the sys.sql_module system view to checkout the script of these objects. But a single space character might make 2 SPs different. We will use the DIFFERENCE function to reduce such discrepencies and focus on the script itself:

-- Changes in the SPs, Views and UDFs

SELECT o.name,

            m.definition

FROM Source.sys.sql_modules m

     INNER JOIN Source.sys.objects o ON m.object_id = o.object_id

     INNER JOIN Destination.sys.sql_modules m2      INNER JOIN Destination.sys.objects o2 ON m2.object_id = o2.object_id ON o.name = o2.name

WHERE DIFFERENCE(m.definition, m2.definition) < 4

I included the different parts in one stored procedure that produces a table with all the differences between the two databases:

CREATE PROCEDURE [dbo].[CompareSchema]

AS

CREATE TABLE #Changes

(

[Name] nvarchar(256),

[Type] nvarchar(256),

[Value] nvarchar(MAX),

[Action] nvarchar(256)

)

 

-- Objects to be created

INSERT INTO #Changes([Name], [Type], [Action])

SELECT name, type_desc, 'Create'

FROM Source.sys.objects

WHERE name NOT IN (SELECT name

FROM Destination.sys.objects)

ORDER BY type_desc, name

-- Objects to be removed

INSERT INTO #Changes([Name], [Type], [Action])

SELECT name, type_desc, 'Delete'

FROM Destination.sys.objects

WHERE name NOT IN (SELECT name

FROM Source.sys.objects)

 

-- Columns to be created

INSERT INTO #Changes([Name], [Type], [Value], [Action])

SELECT TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME,

'COLUMN',

DATA_TYPE + ISNULL('(' + (CASE WHEN CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) END ) + ')' , ISNULL('(' + CONVERT(varchar, NUMERIC_PRECISION) + ', ' + CONVERT(varchar, NUMERIC_SCALE) + ')', '')) + (CASE WHEN IS_NULLABLE = 'YES' THEN ', null' ELSE ', not null' END),

'Create'

FROM Source.INFORMATION_SCHEMA.COLUMNS source

WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Destination.INFORMATION_SCHEMA.COLUMNS destination

WHERE source.TABLE_NAME = destination.TABLE_NAME)

AND TABLE_NAME IN (SELECT TABLE_NAME

FROM Destination.INFORMATION_SCHEMA.TABLES)

-- Columns to be removed

INSERT INTO #Changes([Name], [Type], [Value], [Action])

SELECT TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME,

'COLUMN',

DATA_TYPE + ISNULL('(' + (CASE WHEN CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) END ) + ')' , ISNULL('(' + CONVERT(varchar, NUMERIC_PRECISION) + ', ' + CONVERT(varchar, NUMERIC_SCALE) + ')', '')) + (CASE WHEN IS_NULLABLE = 'YES' THEN ', null' ELSE ', not null' END),

'Delete'

FROM Destination.INFORMATION_SCHEMA.COLUMNS destination

WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Source.INFORMATION_SCHEMA.COLUMNS source

WHERE source.TABLE_NAME = destination.TABLE_NAME)

AND TABLE_NAME IN (SELECT TABLE_NAME

FROM Source.INFORMATION_SCHEMA.TABLES)

-- Columns to be modified

INSERT INTO #Changes([Name], [Type], [Value], [Action])

SELECT source.TABLE_SCHEMA + '.' + source.TABLE_NAME + '.' + source.COLUMN_NAME,

'COLUMN',

source.DATA_TYPE + ISNULL('(' + (CASE WHEN CONVERT(varchar, source.CHARACTER_MAXIMUM_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(varchar, source.CHARACTER_MAXIMUM_LENGTH) END ) + ')' , ISNULL('(' + CONVERT(varchar, source.NUMERIC_PRECISION) + ', ' + CONVERT(varchar, source.NUMERIC_SCALE) + ')', '')) + (CASE WHEN source.IS_NULLABLE = 'YES' THEN ', null' ELSE ', not null' END),

'Update'

FROM Source.INFORMATION_SCHEMA.COLUMNS source

INNER JOIN Destination.INFORMATION_SCHEMA.COLUMNS destination

ON source.TABLE_NAME = destination.TABLE_NAME

AND source.COLUMN_NAME = destination.COLUMN_NAME

WHERE source.COLUMN_DEFAULT <> destination.COLUMN_DEFAULT

OR source.DATA_TYPE <> destination.DATA_TYPE

OR source.CHARACTER_MAXIMUM_LENGTH <> destination.CHARACTER_MAXIMUM_LENGTH OR source.NUMERIC_PRECISION <> destination.NUMERIC_PRECISION

OR source.NUMERIC_SCALE <> destination.NUMERIC_SCALE

-- Changes in the SPs, Views and UDFs

INSERT INTO #Changes([Name], [Type], [Value], [Action])

SELECT o.name,

'SQL_STORED_PROCEDURE',

m.definition,

'Update'

FROM Source.sys.sql_modules m INNER JOIN Source.sys.objects o

ON m.object_id = o.object_id

INNER JOIN Destination.sys.sql_modules m2 INNER JOIN Destination.sys.objects o2

ON m2.object_id = o2.object_id

ON o.name = o2.name

WHERE DIFFERENCE(m.definition, m2.definition) < 4

SELECT *

FROM #Changes

The output table has the following columns:

  1. Name: the name of the object
  2. Type: the type of the object
  3. Value: the value of the object. For the columns, it will include its types, while for the SPs, Views and UDFs, it will include its scripts
  4. Action: one of the 3 possible actions: Create, Delete or Update

Output