Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Cette requête SQL permet d'établir la différence de structure entre 2 schémas de base de données DB2.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.