Vous consultez une page technique concernant le logiciel de gestion Mercator. Celle-ci contient des informations spécifiques destinées aux professionnels de Mercator. Souhaitez-vous être redirigés vers des informations plus générales ?


   Ne plus poser cette question

Comparer la structure de deux bases de données Mercator

0000003205     -      11/04/2024

La requête fournie ci-dessous permet d'établir la liste des champs différents entre deux bases de données.

Vous devez juste adapter le nom des bases de données (MERCATORXXX et MERCATORYYY).

Vous obtiendrez deux listes reprenant

  • la base de données concernée
  • la table
  • le champ
  • le type
  • la longueur
  • le statut (soit "manquant" soit différent")
  • la longueur dans l'autre base de données.
  •  

    use MERCATORXXX
    select table_catalog as Orig, table_name, column_name, data_type, CHARACTER_MAXIMUM_LENGTH
        into dbo.#OrigField
        from information_schema.columns
        order by table_name, column_name

    use MERCATORYYY
    select table_catalog as Orig, table_name, column_name, data_type, CHARACTER_MAXIMUM_LENGTH
        into dbo.#DestField
        from information_schema.columns
        order by table_name, column_name

    select o.orig as 'Database', o.table_name as 'Table', o.column_name as 'Column', o.data_type as 'Type', 
    isnull(o.CHARACTER_MAXIMUM_LENGTH, 0) as 'Longueur', 'manquant' as statut, 0 as 'Longueur Dest'
        from dbo.#OrigField o
            left join dbo.#DestField d on d.TABLE_NAME = o.table_name and d.column_NAME = o.column_name
        where isnull(d.column_name, '') = ''
    union
    select o.orig as 'Database', o.table_name as 'Table', o.column_name as 'Column', o.data_type as 'Type', 
    isnull(o.CHARACTER_MAXIMUM_LENGTH, 0) as 'Longueur', 'différent' as statut, isnull(d.CHARACTER_MAXIMUM_LENGTH, 0) as 'Longueur Dest'
        from dbo.#OrigField o
            left join dbo.#DestField d on d.TABLE_NAME = o.table_name and d.column_NAME = o.column_name
        where d.CHARACTER_MAXIMUM_LENGTH <> o.CHARACTER_MAXIMUM_LENGTH
        order by o.table_name, o.column_name

    select d.orig as 'Database', d.table_name as 'Table', d.column_name as 'Column', d.data_type as 'Type',
    isnull(d.CHARACTER_MAXIMUM_LENGTH, 0) as 'Longueur', 'manquant' as statut, 0 as 'Longueur Dest'
        from dbo.#DestField d
            left join dbo.#OrigField o on o.TABLE_NAME = d.table_name and o.column_NAME = d.column_name
        where isnull(o.column_name, '') = ''
    union
    select d.orig as 'Database', d.table_name as 'Table', d.column_name as 'Column', d.data_type as 'Type',
    isnull(d.CHARACTER_MAXIMUM_LENGTH, 0) as 'Longueur', 'différent' as statut, isnull(o.CHARACTER_MAXIMUM_LENGTH, 0) as 'Longueur Dest'
        from dbo.#DestField d
            left join dbo.#OrigField o on o.TABLE_NAME = d.table_name and o.column_NAME = d.column_name
        where o.CHARACTER_MAXIMUM_LENGTH <> d.CHARACTER_MAXIMUM_LENGTH
        order by d.table_name, d.column_name

    drop table dbo.#DestField
    drop table dbo.#OrigField