Database Schema Comparison Using Simple Queries
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:
-
The new objects
-
The objects that need to be deleted (not present in the Source database but present in the Destination database)
-
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 sourceWHERE 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 destinationWHERE 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 destinationON source.TABLE_NAME = destination.TABLE_NAME AND source.COLUMN_NAME = destination.COLUMN_NAMEWHERE source.COLUMN_DEFAULT <> destination.COLUMN_DEFAULT OR source.DATA_TYPE <> destination.DATA_TYPEOR source.CHARACTER_MAXIMUM_LENGTH <> destination.CHARACTER_MAXIMUM_LENGTH OR source.NUMERIC_PRECISION <> destination.NUMERIC_PRECISION OR source.NUMERIC_SCALE <> destination.NUMERIC_SCALEThe 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
.definitionFROM 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.nameWHERE
DIFFERENCE(m.definition, m2.definition) < 4I 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.objectsWHERE
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.objectsWHERE
name NOT IN (SELECT nameFROM 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 sourceWHERE 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 destinationWHERE 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 destinationON source.TABLE_NAME = destination.TABLE_NAME AND source.COLUMN_NAME = destination.COLUMN_NAMEWHERE source.COLUMN_DEFAULT <> destination.COLUMN_DEFAULT OR source.DATA_TYPE <> destination.DATA_TYPEOR 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_idINNER JOIN Destination.sys.sql_modules m2 INNER JOIN Destination.sys.objects o2 ON m2.object_id = o2.object_idON o.name = o2.nameWHERE
DIFFERENCE(m.definition, m2.definition) < 4SELECT
*FROM #ChangesThe output table has the following columns:
- Name: the name of the object
- Type: the type of the object
- 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
- Action: one of the 3 possible actions: Create, Delete or Update
