Detects Differences Between Databases

• Download source code - 1.2 KB

Introduction

This query is useful to identify the differences between two databases. In particular, the differences that this query help to identify are:

• Tables added
• Renamed tables
• Existing table contains new columns
• Existing table with renamed columns
• Views, procedures, functions and trigger added
• Views, procedures, functions and trigger modified

Procedure and Use

The best situation to use this procedure is when you have an actual database and an old backup of the same dabatase.

DECLARE @Database1 AS NVARCHAR(50)
DECLARE @Database2 AS NVARCHAR(50)
DECLARE @qry AS NVARCHAR(4000)
SET @Database1 = '<NEW DATABASE NAME>'
SET @Database2 = '<OLD DATABASE NAME>'
set @qry =
'WITH view_proc_definition_db1 (objectID,objname, otype, oDefinition)
AS
(
SELECT DISTINCT o.object_id,
o.name AS Object_Name,
o.type_desc,
m.definition
FROM [' + @Database1 + '].sys.sql_modules m
INNER JOIN [' + @Database1 + '].sys.objects o
ON m.object_id=o.object_id
),view_proc_definition_db2 (objectID,objname, otype, oDefinition)
AS
(
SELECT DISTINCT o.object_id,
o.name AS Object_Name,
o.type_desc,
m.definition
FROM [' + @Database2 + '].sys.sql_modules m
INNER JOIN [' + @Database2 + '].sys.objects o
ON m.object_id=o.object_id
),table_definition_db1 (TableID,TableName, ColumnName,ColumnID)
AS
(
SELECT t.object_id,t.name,c.name,c.column_id
FROM [' + @Database1 + '].SYS.tables t
inner join [' + @Database1 + '].SYS.columns c
on t.object_id = c.object_id
),table_definition_db2 (TableID,TableName, ColumnName,ColumnID)
AS
(
SELECT t.object_id,t.name,c.name,c.column_id
FROM [' + @Database2 + '].SYS.tables t
inner join [' + @Database2 + '].SYS.columns c
on t.object_id = c.object_id
),NewTables (TableID,TableName)
AS
(
select DISTINCT td1.TableID,td1.TableName
from table_definition_db1 td1
left outer join table_definition_db2 td2
on td1.TableID = td2.TableID
where td2.TableID is null
),ModifedTables(TableID,TableName)
AS
(
SELECT DISTINCT td1.TableID,td1.TableName
FROM table_definition_db1 td1
INNER JOIN table_definition_db2 td2
ON td1.TableID = td2.TableID
WHERE td1.TableName <> td2.TableName
),TablesWithNewComlumns (TableID,TableName,ColumnsName)
AS
(
SELECT td1.TableID,td1.TableName,td1.ColumnName
FROM table_definition_db1 td1
left outer join table_definition_db2 td2
on td1.TableID = td2.TableID and
TD1.ColumnID = TD2.ColumnID
where td2.ColumnID is null
),TablesWithModifiedComlumns (TableID,TableName,ColumnsName)
AS
(
SELECT td1.TableID,td1.TableName,td1.ColumnName
FROM table_definition_db1 td1
INNER JOIN table_definition_db2 td2
ON td1.TableID = td2.TableID AND
TD1.ColumnID = TD2.ColumnID
WHERE TD1.ColumnName <> TD2.ColumnName
),ModifiedObject (ObjectName,ObjectType,ObjectDefinition)
AS
(
SELECT pd1.objname,pd1.otype,pd1.oDefinition
FROM view_proc_definition_db1 pd1
LEFT OUTER JOIN view_proc_definition_db2 pd2
ON pd1.objectID = pd2.objectID
WHERE (pd1.oDefinition <> pd2.oDefinition) OR pd2.objectID IS NULL
),ModifiedObjectsDetails (Name,StructureType,Descriptions)
AS
(
SELECT ObjectName,ObjectType,ObjectDefinition
FROM ModifiedObject
UNION
SELECT TWNC.TableName,''COLUMNS NAME MODIFIED'', _
Left(Main.ColumnsNames,Len(Main.ColumnsNames)-1) As "ColumnsNames"
FROM
( SELECT DISTINCT ST2.TableID,
(
SELECT ST1.ColumnsName + '','' AS [text()]
FROM TablesWithModifiedComlumns ST1
WHERE ST1.TableID = ST2.TableID
ORDER BY ST1.TableID
FOR XML PATH ('''')
) [ColumnsNames]
FROM TablesWithModifiedComlumns ST2
) [Main]
INNER JOIN TablesWithModifiedComlumns TWNC
ON MAIN.TableID = TWNC.TableID
UNION

SELECT TWNC.TableName,''TABLE WITH NEW COLUMNS'', _
Left(Main.ColumnsNames,Len(Main.ColumnsNames)-1) As "ColumnsNames"
FROM
( SELECT DISTINCT ST2.TableID,
(
SELECT ST1.ColumnsName + '','' AS [text()]
FROM TablesWithNewComlumns ST1
WHERE ST1.TableID = ST2.TableID
ORDER BY ST1.TableID
FOR XML PATH ('''')
) [ColumnsNames]
FROM TablesWithNewComlumns ST2
) [Main]
INNER JOIN TablesWithNewComlumns TWNC
ON MAIN.TableID = TWNC.TableID

UNION
SELECT TableName, ''NEW TABLE'', NULL
FROM NewTables
UNION
SELECT TableName, ''TABLE NAME MODIFIED'', NULL
FROM ModifedTables
)

SELECT *
FROM ModifiedObjectsDetails
ORDER BY StructureType'
EXECUTE sp_executesql @qry