Cette requête SQL permet d'établir la différence de structure entre 2 schémas de base de données DB2.

schema_diff.sql

SELECT DISTINCT 
	schemaR.name AS tbname, 		
	'' AS name, 	
	'' AS colno,
	CASE 
		WHEN schemaA.name IS NULL AND schemaB.name IS NOT NULL THEN 'Table créée'
		WHEN schemaA.name IS NOT NULL AND schemaB.name IS NULL THEN 'Table supprimée'				
	END AS typeModif,
	'' AS coltype,
	'' AS length,
	'' AS default,
	'' AS nulls		
FROM sysibm.systables AS schemaR
LEFT JOIN sysibm.systables AS schemaA
	ON schemaR.name = schemaA.name
	AND schemaA.creator = '__SCHEMA_A__'
LEFT JOIN sysibm.systables AS schemaB
	ON schemaR.name = schemaB.name
	AND schemaB.creator = '__SCHEMA_B__'
WHERE schemaR.creator IN ('__SCHEMA_A__', '__SCHEMA_B__')
AND schemaR.type = 'T'
AND (
		schemaA.name IS NULL
	OR 
		schemaB.name IS NULL
)
UNION
SELECT DISTINCT
	schemaR.tbname, 
	schemaR.name, 	
	CAST(schemaR.colno AS CHAR) AS colno,
	CASE 
		WHEN schemaA.name IS NULL AND schemaB.name IS NOT NULL THEN 'Champ créé'
		WHEN schemaA.name IS NOT NULL AND schemaB.name IS NULL THEN 'Champ supprimé'		
		ELSE 'Champ modifié'
	END AS typeModif,
	CASE 
		WHEN schemaA.coltype <> schemaB.coltype THEN LTRIM(RTRIM(schemaA.coltype)) || ' > ' || LTRIM(RTRIM(schemaB.coltype))
		ELSE ''
	END AS coltype,
	CASE 
		WHEN schemaA.length <> schemaB.length THEN LTRIM(RTRIM(schemaA.length)) || ' > ' || LTRIM(RTRIM(schemaB.length))
		ELSE ''
	END AS length,
	CASE 
		WHEN schemaA.default <> schemaB.default THEN LTRIM(RTRIM(CAST(schemaA.default AS CHAR))) || ' > ' || LTRIM(RTRIM(CAST(schemaB.default AS CHAR)))
		ELSE ''
	END AS default,
	CASE 
		WHEN schemaA.nulls <> schemaB.nulls THEN LTRIM(RTRIM(schemaA.nulls)) || ' > ' || LTRIM(RTRIM(schemaB.nulls))
		ELSE ''
	END AS nulls
FROM sysibm.syscolumns AS schemaR
INNER JOIN sysibm.systables
	ON schemaR.tbname = sysibm.systables.name 
	AND schemaR.tbcreator = sysibm.systables.creator
LEFT JOIN sysibm.syscolumns AS schemaA
	ON schemaR.tbname = schemaA.tbname
	AND schemaR.name = schemaA.name
	AND schemaA.tbcreator = '__SCHEMA_A__'
LEFT JOIN sysibm.syscolumns AS schemaB
	ON schemaR.tbname = schemaB.tbname
	AND schemaR.name = schemaB.name
	AND schemaB.tbcreator = '__SCHEMA_B__'	
WHERE schemaR.tbcreator IN ('__SCHEMA_A__', '__SCHEMA_B__')
AND sysibm.systables.type = 'T'
AND (schemaR.tbname = schemaA.tbname OR schemaR.tbname = schemaB.tbname)
AND (
	   	schemaA.coltype <> schemaB.coltype
	OR 
		schemaA.length <> schemaB.length
	OR 
		schemaA.default <> schemaB.default
	OR 
		schemaA.nulls <> schemaB.nulls
	OR 	
		schemaA.name IS NULL
	OR 
		schemaB.name IS NULL
)
AND (
		EXISTS (
			SELECT * 
			FROM sysibm.systables
			WHERE schemaA.tbname = sysibm.systables.name
			AND sysibm.systables.creator = '__SCHEMA_B__'
		)
	OR 
		EXISTS (
			SELECT * 
			FROM sysibm.systables
			WHERE schemaB.tbname = sysibm.systables.name
			AND sysibm.systables.creator = '__SCHEMA_A__'
		)
)
ORDER BY tbname, colno