Vergelijking van de structuur van twee Mercator databases

De onderstaande query geeft een overzicht van de verschillende velden tussen twee databases.

Je hoeft alleen maar de namen van de databases aan te passen (MERCATORXXX en MERCATORYYY).

Je krijgt twee lijsten met 

• de betreffende database
• de tabel
• het veld
• het type
• de lengte
• de status (ofwel "ontbrekend" ofwel "verschillend")
• de lengte in de andere database

 

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