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 source
WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Destination.INFORMATION_SCHEMA.COLUMNS destinationWHERE source.TABLE_NAME = destination.TABLE_NAME)
AND TABLE_NAME IN (SELECT TABLE_NAMEFROM 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 sourceWHERE source.TABLE_NAME = destination.TABLE_NAME)
AND TABLE_NAME IN (SELECT TABLE_NAMEFROM 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 sourceINNER JOIN Destination.INFORMATION_SCHEMA.COLUMNS destination
ON source.TABLE_NAME = destination.TABLE_NAMEAND source.COLUMN_NAME = destination.COLUMN_NAME
WHERE source.COLUMN_DEFAULT <> destination.COLUMN_DEFAULTOR source.DATA_TYPE <> destination.DATA_TYPE
OR source.CHARACTER_MAXIMUM_LENGTH <> destination.CHARACTER_MAXIMUM_LENGTH OR source.NUMERIC_PRECISION <> destination.NUMERIC_PRECISIONOR 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 mINNER 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) < 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 destinationWHERE source.TABLE_NAME = destination.TABLE_NAME)
AND TABLE_NAME IN (SELECT TABLE_NAMEFROM 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 sourceWHERE source.TABLE_NAME = destination.TABLE_NAME)
AND TABLE_NAME IN (SELECT TABLE_NAMEFROM 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 sourceINNER JOIN Destination.INFORMATION_SCHEMA.COLUMNS destination
ON source.TABLE_NAME = destination.TABLE_NAMEAND source.COLUMN_NAME = destination.COLUMN_NAME
WHERE source.COLUMN_DEFAULT <> destination.COLUMN_DEFAULTOR source.DATA_TYPE <> destination.DATA_TYPE
OR source.CHARACTER_MAXIMUM_LENGTH <> destination.CHARACTER_MAXIMUM_LENGTH OR source.NUMERIC_PRECISION <> destination.NUMERIC_PRECISIONOR 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 oON m.object_id = o.object_id
INNER JOIN Destination.sys.sql_modules m2 INNER JOIN Destination.sys.objects o2ON m2.object_id = o2.object_id
ON o.name = o2.nameWHERE DIFFERENCE(m.definition, m2.definition) < 4
SELECT *
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
