Como programador en múltiples ocasiones me he visto obligado a confirmar que en la base de datos en PRODUCCIÓN del cliente se tienen iguales tablas, vistas, procedimientos, disparadores/triggers, etc. etc. comparando contra la base de datos de PRUEBAS y/o de DESARROLLO.
Los comandos SQL para comparar tablas, vistas y llaves foráneas ya los tenía en el "almacén" de comandos útiles de MySQL que tengo.
He desarrollado un procedimiento almacenado que me permite sacar listados de diferencias entre bases de datos indicando los nombres de estas y un parámetro que indica que tipo de diferencias son las que quiero ver.
Respuestas posibles del procedimiento aquí publicado:use master; go if exists (select * from sys.objects where name = 'sp_database_comparator') drop proc sp_database_comparator; go /* *** PROCEDIMIENTO COMPARADOR DE BASES DE DATOS [ sp_database_comparator ] *** Parámetros @db1 => Nombre de base de datos I @db2 => Nombre de base de datos II @opt => Opción de comparación @excludeObjects_SELECT => - Script con una lista de objetos a no tomar en cuenta - Solo se toma en cuenta para opciones 1 y 2 - Ejemplo: 'select ''cat_depto'' union select ''cat_pais''' Opciones de comparación 1: Búsqueda de objetos que existen en alguna de las bases de datos pero no en la otra 2: Búsqueda de columnas por objetos que existen en alguna de las bases de datos pero no en la otra 3: Comparación de columnas a detalle :: Se muestran items que tengan diferencias 4: Comparación de existencia de rutinas 5: Comparación de parámetros de rutinas existentes 6: Comparación de existencia de llaves foráneas */ CREATE proc sp_database_comparator ( @db1 varchar(100), @db2 varchar(100), @opt int, @excludeObjects_SELECT varchar(max) = null ) as declare @qry varchar(max); set nocount on; -- Tabla temporal para exclusión de objetos por nombre create table #excludeObjects(OBJECT_NAME varchar(200)); if (not @excludeObjects_SELECT is null) and not @excludeObjects_SELECT = '' exec ('insert into #excludeObjects ' + @excludeObjects_SELECT); insert into #excludeObjects select 'sp_renamediagram' union select 'sp_helpdiagramdefinition' union select 'sp_alterdiagram' union select 'sp_creatediagram' union select 'sp_dropdiagram' union select 'fn_diagramobjects' union select 'sp_upgraddiagrams' union select 'sp_helpdiagrams' union select 'sysdiagrams'; -- Búsqueda de objetos que existen en alguna de las bases de datos pero no en la otra if (@opt = 1) begin set @qry = ' select a.name as OBJECT_NAME, a.type as OBJECT_TYPE , case when b.name is null then '''' else ''X'' end IN_DB1, case when C.name is null then '''' else ''X'' end IN_DB2 from ( select a.name, a.type from ~DB1~.sys.objects a where not a.type in (''S'', ''F'', ''PK'', ''D'', ''IT'', ''SQ'', ''UQ'') union select a.name, a.type from ~DB2~.sys.objects a where not a.type in (''S'', ''F'', ''PK'', ''D'', ''IT'', ''SQ'', ''UQ'') ) a left outer join ~DB1~.sys.objects b on b.name = a.name and b.type = a.type left outer join ~DB2~.sys.objects c on c.name = a.name and c.type = a.type left outer join #excludeObjects exc on exc.OBJECT_NAME = a.name where (b.name is null or c.name is null) and exc.OBJECT_NAME is null order by 2, 1'; end; -- Búsqueda de columnas por objetos que existen en alguna de las bases de datos pero no en la otra if (@opt = 2) begin set @qry = ' select a.OBJECT_NAME, a.COLUMN_NAME, case when b.COLUMN_NAME is null then '''' else ''X'' end IN_DB1, case when C.COLUMN_NAME is null then '''' else ''X'' end IN_DB2 from ( select b.name as OBJECT_NAME, a.name as COLUMN_NAME from ~DB1~.sys.columns a inner join ~DB1~.sys.objects b on b.object_id = a.object_id and not b.type in (''S'', ''F'', ''PK'', ''D'', ''IT'', ''SQ'', ''UQ'') union select b.name as OBJECT_NAME, a.name as COLUMN_NAME from ~DB2~.sys.columns a inner join ~DB2~.sys.objects b on b.object_id = a.object_id and not b.type in (''S'', ''F'', ''PK'', ''D'', ''IT'', ''SQ'', ''UQ'') ) a left outer join (select b.name as OBJECT_NAME, a.name as COLUMN_NAME from ~DB1~.sys.columns a inner join ~DB1~.sys.objects b on b.object_id = a.object_id and not b.type in (''S'', ''F'', ''PK'', ''D'', ''IT'', ''SQ'', ''UQ'')) b on b.OBJECT_NAME = a. OBJECT_NAME and b.COLUMN_NAME = a.COLUMN_NAME left outer join (select b.name as OBJECT_NAME, a.name as COLUMN_NAME from ~DB2~.sys.columns a inner join ~DB2~.sys.objects b on b.object_id = a.object_id and not b.type in (''S'', ''F'', ''PK'', ''D'', ''IT'', ''SQ'', ''UQ'')) c on c.OBJECT_NAME = a. OBJECT_NAME and c.COLUMN_NAME = a.COLUMN_NAME left outer join #excludeObjects exc on exc.OBJECT_NAME = a.OBJECT_NAME where exc.OBJECT_NAME is null and (b.COLUMN_NAME is null or c.COLUMN_NAME is null) order by 1, 2'; end; -- Comparación de columnas a detalle :: Se muestran items que tengan diferencias if (@opt = 3) begin set @qry = 'select a.TABLE_NAME, a.COLUMN_NAME, a.COLUMN_DEF as COLUMN_DEF_DB1, a.N as N_DB1, a.IS_NULLABLE as IS_NULLABLE_DB1, a.COLLATION_NAME as COLLATION_NAME_DB1, a.DF_VALUE as DF_VALUE_DB1, b.COLUMN_DEF as COLUMN_DEF_DB2, b.N as N_DB2, b.IS_NULLABLE as IS_NULLABLE_DB2, b.COLLATION_NAME as COLLATION_NAME_DB2, b.DF_VALUE as DF_VALUE_DB2 from ( select b.name as TABLE_NAME, a.name as COLUMN_NAME, c.name + case when c.name in (''decimal'') then '' ('' + convert(varchar(10), a.precision) + '', '' + convert(varchar(10), a.scale) + '')'' when c.name in (''char'', ''varchar'', ''nchar'', ''nvarchar'') then '' ('' + case when (isnull(a.max_length, 0) = -1) then ''max'' else convert(varchar(20), a.max_length) end + '')'' else '''' end + case when (isnull(a.is_identity, 0) = 1) then '' identity ('' + convert(varchar(10), d.seed_value) + '', '' + convert(varchar(10), d.increment_value) + '')'' else '''' end as COLUMN_DEF, a.column_id as N, a.is_nullable as IS_NULLABLE, a.collation_name as COLLATION_NAME, e.DF_VALUE from ~DB1~.sys.columns a inner join ~DB1~.sys.objects b on b.object_id = a.object_id and not b.type in (''S'', ''F'', ''PK'', ''D'', ''IT'', ''SQ'', ''UQ'') left outer join ~DB1~.sys.systypes c on c.xtype = a.user_type_id left outer join ~DB1~.sys.identity_columns d on d.object_id = a.object_id and d.name = a.name left outer join ( select b.name as TABLE_NAME, c.name as COLUMN_NAME, a.name as DF_NAME, a.[definition] as DF_VALUE, a.object_id as default_object_id from ~DB1~.sys.default_constraints a inner join ~DB1~.sys.objects b on b.object_id = a.parent_object_id inner join ~DB1~.sys.columns c on c.object_id = a.parent_object_id and c.column_id = a.parent_column_id ) e on e.TABLE_NAME = b.name and e.COLUMN_NAME = a.name where c.name <> ''sysname'' ) a inner join ( select b.name as TABLE_NAME, a.name as COLUMN_NAME, c.name + case when c.name in (''decimal'') then '' ('' + convert(varchar(10), a.precision) + '', '' + convert(varchar(10), a.scale) + '')'' when c.name in (''char'', ''varchar'', ''nchar'', ''nvarchar'') then '' ('' + case when (isnull(a.max_length, 0) = -1) then ''max'' else convert(varchar(20), a.max_length) end + '')'' else '''' end + case when (isnull(a.is_identity, 0) = 1) then '' identity ('' + convert(varchar(10), d.seed_value) + '', '' + convert(varchar(10), d.increment_value) + '')'' else '''' end as COLUMN_DEF, a.column_id as N, a.is_nullable as IS_NULLABLE, a.collation_name as COLLATION_NAME, e.DF_VALUE from ~DB2~.sys.columns a inner join ~DB2~.sys.objects b on b.object_id = a.object_id and not b.type in (''S'', ''F'', ''PK'', ''D'', ''IT'', ''SQ'', ''UQ'') left outer join ~DB2~.sys.systypes c on c.xtype = a.user_type_id left outer join ~DB2~.sys.identity_columns d on d.object_id = a.object_id and d.name = a.name left outer join ( select b.name as TABLE_NAME, c.name as COLUMN_NAME, a.name as DF_NAME, a.[definition] as DF_VALUE, a.object_id as default_object_id from ~DB2~.sys.default_constraints a inner join ~DB2~.sys.objects b on b.object_id = a.parent_object_id inner join ~DB2~.sys.columns c on c.object_id = a.parent_object_id and c.column_id = a.parent_column_id ) e on e.TABLE_NAME = b.name and e.COLUMN_NAME = a.name where c.name <> ''sysname'' ) b on b.TABLE_NAME = a.TABLE_NAME and b.COLUMN_NAME = a.COLUMN_NAME where not a.COLUMN_DEF = b.COLUMN_DEF or not a.N = b.N or not a.IS_NULLABLE = b.IS_NULLABLE or not a.COLLATION_NAME = b.COLLATION_NAME or not a.DF_VALUE = b.DF_VALUE'; end; -- Comparación de existencia de rutinas if (@opt = 4) begin set @qry = ' select a.ROUTINE_NAME, a.ROUTINE_TYPE, case when b.object_id is null then '''' else ''X'' end as IN_DB1, case when c.object_id is null then '''' else ''X'' end as IN_DB2 from ( select name as ROUTINE_NAME, type_desc as ROUTINE_TYPE from ~DB1~.sys.objects where type in (''P'', ''TR'', ''FN'') union select name as ROUTINE_NAME, type_desc as ROUTINE_TYPE from ~DB2~.sys.objects where type in (''P'', ''TR'', ''FN'') ) a left outer join ~DB1~.sys.objects b on b.name = a.ROUTINE_NAME and b.type_desc = a.ROUTINE_TYPE left outer join ~DB2~.sys.objects c on c.name = a.ROUTINE_NAME and c.type_desc = a.ROUTINE_TYPE left outer join #excludeObjects exc on exc.OBJECT_NAME = a.ROUTINE_NAME where (b.object_id is null or c.object_id is null) and exc.OBJECT_NAME is null'; end; -- Comparación de parámetros de rutinas existentes if (@opt = 5) begin set @qry = ' select a.ROUTINE_TYPE, a.ROUTINE_NAME, a.PARAMETER_ID, a.PARAMETER_NAME, case when b.ROUTINE_NAME is null then '''' else ''X'' end IN_DB1, case when C.ROUTINE_NAME is null then '''' else ''X'' end IN_DB2, case when b.ROUTINE_NAME is null or c.ROUTINE_NAME is null then '''' else case when b.XDEF <> c.XDEF then ''X'' else '''' end end as DISTINCT_DEF, b.XDEF as DEF_IN_DB1, c.XDEF as DEF_IN_DB2 from ( select a.name as ROUTINE_NAME, a.type_desc as ROUTINE_TYPE, b.parameter_id as PARAMETER_ID, isnull(b.name, '''') as PARAMETER_NAME from ~DB1~.sys.objects a inner join ~DB1~.sys.parameters b on b.object_id = a.object_id union select a.name as ROUTINE_NAME, a.type_desc as ROUTINE_TYPE, b.parameter_id as PARAMETER_ID, isnull(b.name, '''') as PARAMETER_NAME from ~DB2~.sys.objects a inner join ~DB2~.sys.parameters b on b.object_id = a.object_id ) a left outer join ( select a.name as ROUTINE_NAME, a.type_desc as ROUTINE_TYPE, b.parameter_id as PARAMETER_ID, isnull(b.name, '''') as PARAMETER_NAME, isnull( convert(varchar(10), b.parameter_id) + '' :: '' + convert(varchar(300), b.name) + '' :: '' + convert(varchar(300), c.name) + '' :: '' + convert(varchar(20), b.max_length) + '' :: '' + convert(varchar(10), b.precision) + '' :: '' + convert(varchar(30), b.scale) + '' :: '' + convert(varchar(30), b.is_output) + '' :: '' + convert(varchar(100), isnull(default_value, '''')) , '''') as XDEF from ~DB1~.sys.objects a inner join ~DB1~.sys.parameters b on b.object_id = a.object_id left outer join ~DB1~.sys.types c on c.user_type_id = b.user_type_id ) b on b.ROUTINE_TYPE = a.ROUTINE_TYPE and b.ROUTINE_NAME = a.ROUTINE_NAME and b.PARAMETER_ID = a.PARAMETER_ID and b.PARAMETER_NAME = a.PARAMETER_NAME left outer join ( select a.name as ROUTINE_NAME, a.type_desc as ROUTINE_TYPE, b.parameter_id as PARAMETER_ID, isnull(b.name, '''') as PARAMETER_NAME, isnull( convert(varchar(10), b.parameter_id) + '' :: '' + convert(varchar(300), b.name) + '' :: '' + convert(varchar(300), c.name) + '' :: '' + convert(varchar(20), b.max_length) + '' :: '' + convert(varchar(10), b.precision) + '' :: '' + convert(varchar(30), b.scale) + '' :: '' + convert(varchar(30), b.is_output) + '' :: '' + convert(varchar(100), isnull(default_value, '''')) , '''') as XDEF from ~DB1~.sys.objects a inner join ~DB1~.sys.parameters b on b.object_id = a.object_id left outer join ~DB1~.sys.types c on c.user_type_id = b.user_type_id ) c on c.ROUTINE_TYPE = a.ROUTINE_TYPE and c.ROUTINE_NAME = a.ROUTINE_NAME and c.PARAMETER_ID = a.PARAMETER_ID and c.PARAMETER_NAME = a.PARAMETER_NAME left outer join #excludeObjects exc on exc.OBJECT_NAME = a.ROUTINE_NAME where exc.OBJECT_NAME is null and ( b.ROUTINE_NAME is null or c.ROUTINE_NAME is null or case when b.ROUTINE_NAME is null or c.ROUTINE_NAME is null then '''' else case when b.XDEF <> c.XDEF then ''X'' else '''' end end = ''X'' )'; end; -- Comparación de existencia de llaves foráneas if (@opt = 6) begin set @qry = 'select a.TABLE_NAME, a.REF_TABLE_NAME, a.FK, case when b.TABLE_NAME is null then '''' else ''X'' end IN_DB1, case when c.TABLE_NAME is null then '''' else ''X'' end IN_DB2 from ( select b.name as TABLE_NAME, c.name as REF_TABLE_NAME, a.name as FK from ~DB1~.sys.foreign_keys a inner join ~DB1~.sys.objects b on b.object_id = a.parent_object_id inner join ~DB1~.sys.objects c on c.object_id = a.referenced_object_id union select b.name as TABLE_NAME, c.name as REF_TABLE_NAME, a.name as FK from ~DB2~.sys.foreign_keys a inner join ~DB2~.sys.objects b on b.object_id = a.parent_object_id inner join ~DB2~.sys.objects c on c.object_id = a.referenced_object_id ) a left outer join ( select b.name as TABLE_NAME, c.name as REF_TABLE_NAME, a.name as FK from ~DB1~.sys.foreign_keys a inner join ~DB1~.sys.objects b on b.object_id = a.parent_object_id inner join ~DB1~.sys.objects c on c.object_id = a.referenced_object_id ) b on b.TABLE_NAME = a.TABLE_NAME and b.REF_TABLE_NAME = a.REF_TABLE_NAME and b.FK = a.FK left outer join ( select b.name as TABLE_NAME, c.name as REF_TABLE_NAME, a.name as FK from ~DB2~.sys.foreign_keys a inner join ~DB2~.sys.objects b on b.object_id = a.parent_object_id inner join ~DB2~.sys.objects c on c.object_id = a.referenced_object_id ) c on c.TABLE_NAME = a.TABLE_NAME and c.REF_TABLE_NAME = a.REF_TABLE_NAME and c.FK = a.FK where b.TABLE_NAME is null or c.TABLE_NAME is null'; end; set @qry = replace(REPLACE(@qry, '~DB2~', @db2), '~DB1~', @db1); set nocount off; exec (@qry); go
declare @db1 varchar(100), @db2 varchar(100); set @db1 = 'dbNorthWind_001'; set @db2 = 'dbNorthWind_002'; exec sp_database_comparator @db1, @db2, 1, 'select ''cat_depto'' union select ''cat_pais'''; exec sp_database_comparator @db1, @db2, 2 exec sp_database_comparator @db1, @db2, 3; exec sp_database_comparator @db1, @db2, 4; exec sp_database_comparator @db1, @db2, 5; exec sp_database_comparator @db1, @db2, 6; go
A continuación podrán ver comentarios sobre el resultado de cada una de las opciones que se pueden utilizar en el procedimiento.
Opción de consulta | Columna del resultado | Comentario |
---|---|---|
[ 1 ] Objetos | OBJECT_NAME | Nombre de objeto |
OBJECT_TYPE | Tipo de objeto | |
IN_DB1 | "X" si existe en DB1 | |
IN_DB2 | "X" si existe en DB2 | |
[ 2 ] Columnas | OBJECT_NAME | Nombre de objeto |
COLUMN_NAME | Nombre de columna | |
IN_DB1 | "X" si existe en DB1 | |
IN_DB2 | "X" si existe en DB2 | |
[ 3 ] Columnas a detalle | TABLE_NAME | Nombre de tabla |
COLUMN_NAME | Nombre de columna | |
COLUMN_DEF_DB1 | En DB1: Definición de columna | |
N_DB1 | En DB1: Correlativo de columna | |
IS_NULLABLE_DB1 | En DB1: Acepta nulos | |
COLLATION_NAME_DB1 | En DB1: Nombre de colación | |
DF_VALUE_DB1 | En DB1: Valor por omisión | |
COLUMN_DEF_DB2 | En DB2: Definición de columna | |
N_DB2 | En DB2: Correlativo de columna | |
IS_NULLABLE_DB2 | En DB2: Acepta nulos | |
COLLATION_NAME_DB2 | En DB2: Nombre de colación | |
DF_VALUE_DB2 | En DB2: Valor por omisión | |
[ 4 ] Rutinas | ROUTINE_NAME | Nombre de rutina |
ROUTINE_TYPE | Tipo de rutina | |
IN_DB1 | "X" si existe en DB1 | |
IN_DB2 | "X" si existe en DB2 | |
[ 5 ] Parámetros por rutina | ROUTINE_TYPE | Tipo de rutina |
ROUTINE_NAME | Nombre de rutina | |
PARAMETER_ID | Correlativo de parámetro | |
PARAMETER_NAME | Nombre de parámetro | |
IN_DB1 | "X" si existe en DB1 | |
IN_DB2 | "X" si existe en DB2 | |
DISTINCT_DEF | "X" si la definición del parámetro es distinta | |
DEF_IN_DB1 | En DB1: Valor por omisión del parámetro | |
DEF_IN_DB2 | En DB2: Valor por omisión del parámetro | |
[ 6 ] Comparación de existencia de llaves foráneas | TABLE_NAME | Nombre de la tabla |
REF_TABLE_NAME | Nombre de la tabla referenciada | |
FK | Nombre de la llave foránea | |
IN_DB1 | "X" si existe en DB1 | |
IN_DB2 | "X" si existe en DB2 |
Tipo | Fecha | Asunto |
---|---|---|
Corrección | 2021-02-14 |
Me tope con un problema en la ejecución de un SELECT porque la longitud excedía a los 4000 bytes. Se ha cambiado la declaración de la variable @qry:
Anterior: declare @qry varchar(4000);
Nuevo: declare @qry varchar(max); |
Corrección | 2021-02-14 |
A la opción #5, de comparación de parámetros de rutinas (procedimientos, funciones), se agregó la exclusión de los objetos que se manejan en la lista de la tabla temporal #excludeObjects. |