Come confrontare le strutture di due database

Nel precedente articolo, abbiamo visto come utilizzare INFORMATION_SCHEMA per avere informazioni sui metadati di un database.

Ora vedremo come sfruttare questo strumento per confrontare le strutture di due database con l'obiettivo di evidenziare le differenze tra di essi.

JOIN: breve ripasso

Attraverso l'utilizzo delle JOIN, è possibile leggere dati correlati tra due o più tabelle.

Le condizioni di JOIN indicano al motore SQL come leggere le righe da una tabella in funzione dei dati presenti sulle righe di un'altra tabella e sono espresse tramite i comandi SQL:

• INNER JOIN: seleziona e mette in relazione le righe di due tabelle per le quali c'è una corrispondenza dei valori nelle colonne indicate per la correlazione:
  
• LEFT OUTER JOIN (LEFT JOIN): seleziona tutte le righe della prima tabella (quella di sinistra) con le righe della seconda tabella (quella di destra) che soddisfano la condizione di join. Le righe della prima tabella senza corrispondenza nella seconda sono estese con valori nulli:
  
• RIGHT OUTER JOIN (RIGHT JOIN): lavora in modo simmetrico rispetto alla LEFT OUTER JOIN, ovvero seleziona tutte le righe della seconda tabella (quella di destra) con le righe della prima tabella (quella di sinistra) che soddisfano la condizione di join. Le righe della seconda tabella senza corrispondenza nella prima sono estese con valori nulli:
  
• FULL OUTER JOIN (FULL JOIN): seleziona tutte le righe della prima e della seconda tabella, ottenute con LEFT OUTER JOIN e RIGHT OUTER JOIN:

Quale JOIN utilizzare per il nostro obiettivo?

Attraverso l'operazione di FULL OUTER JOIN (equivalente a FULL JOIN) possiamo determinare le tabelle definite soltanto in uno dei due database. Per fare questo, è sufficiente applicare questo tipo di JOIN alle tabelle DB_Source.INFORMATION_SCHEMA.TABLES e DB_Target.INFORMATION_SCHEMA.TABLES, dove DB_Source e DB_Target sono i due database da analizzare:

-- Search for different tables
SELECT ISNULL(SRC.TABLE_TYPE, TGT.TABLE_TYPE) AS [SCHEMA],
       ISNULL(SRC.TABLE_SCHEMA, TGT.TABLE_SCHEMA) AS [SCHEMA],
       ISNULL(SRC.TABLE_NAME, TGT.TABLE_NAME) AS [NAME],
	   'Defined in ' + CASE WHEN SRC.TABLE_CATALOG IS NULL THEN 'Target' ELSE 'Source' END + 
	   ' but missing in ' + CASE WHEN SRC.TABLE_CATALOG IS NULL THEN 'Source' ELSE 'Target' END AS [NOTE]
FROM DB_Source.INFORMATION_SCHEMA.TABLES AS SRC
	FULL OUTER JOIN DB_Target.INFORMATION_SCHEMA.TABLES AS TGT
		ON SRC.TABLE_SCHEMA = TGT.TABLE_SCHEMA
		AND SRC.TABLE_NAME = TGT.TABLE_NAME
		AND SRC.TABLE_TYPE = TGT.TABLE_TYPE
WHERE SRC.TABLE_CATALOG IS NULL
OR TGT.TABLE_CATALOG IS NULL

Il risultato della query è:

In modo analogo, possiamo determinare le differenze anche a livello di colonne:

-- Search for different columns on same tables
SELECT  SRC.TABLE_CATALOG, SRC.TABLE_SCHEMA, SRC.TABLE_NAME, SRC.COLUMN_NAME,
		TGT.TABLE_CATALOG, TGT.TABLE_SCHEMA, TGT.TABLE_NAME, TGT.COLUMN_NAME,
		'||',*
FROM DB_Source.INFORMATION_SCHEMA.COLUMNS AS SRC
	FULL OUTER JOIN DB_Target.INFORMATION_SCHEMA.COLUMNS AS TGT
		ON SRC.TABLE_SCHEMA = TGT.TABLE_SCHEMA
		AND SRC.TABLE_NAME = TGT.TABLE_NAME
		AND SRC.COLUMN_NAME = TGT.COLUMN_NAME
WHERE ISNULL(SRC.TABLE_SCHEMA, '') <> ISNULL(TGT.TABLE_SCHEMA, '')
OR ISNULL(SRC.TABLE_NAME, '') <> ISNULL(TGT.TABLE_NAME, '')
OR ISNULL(SRC.COLUMN_NAME, '') <> ISNULL(TGT.COLUMN_NAME, '')

Questo script evidenzia le colonne definite soltanto in uno dei due database.

Sono elencate anche tutte le colonne appartenenti a tabelle presenti soltanto in un database.

Il risultato della query è:

Con la stessa tecnica, è possibile determinare le differenze anche per quanto riguarda le altre tipologie di entità definite su un database, come vincoli d'integrità, indici, chiavi primarie, stored procedure o function, trigger.