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