Compare multiple DB schemas

Sometimes you may need to do a quick examination on how are the DBs, that are supposed to have the same schema, different. This quick query allows you to seek for the outlying and the intersecting columns across multiple databases. If you are looking only for the outlying columns and in which DBs they are, the code below is to be used. If you are looking for the intersection of multiple DBs, just switch from HAVING COUNT(0) <> to HAVING COUNT(0) = and remove the list of databases that the columns are in..

In my example, I created 3 DBs Test 1-3 with some differences between the schemas. The key determining a unique column is defined in the Compound_key column.

schema_compare

IF OBJECT_ID('tempdb..#DB_Schema_Compare') IS NOT NULL
DROP TABLE #DB_Schema_Compare;
CREATE TABLE #DB_Schema_Compare
( [DB_Name] NVARCHAR(100),
[Compound_Key] NVARCHAR(Max)
)

USE Test3;

INSERT INTO #DB_Schema_Compare
SELECT
'Test3',
--s.name SchemaName,
--t.name TableName,
--c.name ColumnName,
--c.is_computed ColumnIsComputed,
--c.is_identity ColumnIsIdentity,
--c.is_nullable ColumnIsNullable,
--c.collation_name ColumnCollationName,
--c.max_length ColumnMaxLength,
CAST(
CAST(s.name AS VARCHAR(200)) + '^' +
CAST(t.name AS VARCHAR(200)) + '^' +
CAST(c.name AS VARCHAR(200)) + '^' +
CAST(c.is_computed AS VARCHAR(1)) + '^' +
CAST(c.is_identity AS VARCHAR(1)) + '^' +
CAST(c.is_nullable AS VARCHAR(1)) + '^' +
ISNULL(CAST(c.collation_name AS VARCHAR(200)) + '^','^') +
CAST(c.max_length AS VARCHAR(10)) + '^' +
CAST(ty.name AS VARCHAR(200))
AS VARCHAR(MAX)) CompoundKey
FROM sys.columns c
inner join sys.tables t on c.object_id = t.object_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.types ty on ty.system_type_id = c.system_type_id;

SELECT
i.*,
STUFF(( SELECT ',' + SUB.[DB_Name] AS [text()]
FROM
#DB_Schema_Compare SUB
WHERE
SUB.Compound_Key = i.Compound_Key
ORDER BY SUB.[DB_Name]
FOR XML PATH('')
), 1, 1, '' )
AS [DBs_we_HAVE_this_compound_key_in]
FROM (
     SELECT
     COUNT(0) CNT,
     Compound_Key
     FROM
     #DB_Schema_Compare
     GROUP BY [Compound_Key]
     HAVING COUNT(0) <> (SELECT COUNT(DISTINCT [DB_Name]) FROM #DB_Schema_Compare)
) i;

Advertisements