Bases de datos

SQL Server

Diferencias entre bases de datos

Última actualización: 14-02-2021 12:18

Índice

• Notas
• El procedimiento MSSQL
• Cómo usar el procedimiento
• Notas acerca de qué hay en cada una de las respuestas posibles
• Correcciones / Actualizaciones realizadas

Notas

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:

• Objetos (tablas, vistas, procedimientos, disparadores, funciones, etc. etc.) no existentes en alguna de la base de datos.
• Columnas por tabla/vista que no existen en alguna base de datos
• Columnas cuya definición es distinta.
• Funciones/procedimientos que no existen en alguna base de datos.
• Parámetros por función/procedimiento que no existan en alguna base de datos o cuya definición es distinta.
• Llaves foráneas no existentes en alguna de las bases de datos.

Importante:

• En MSSQL no es posible igual que en MySQL comparar la definición de rutinas (procedimientos, funciones, disparadores).
  • Ya se encontró cómo hacerlo y se evaluará agregarlo a esta publicación en el futuro.
• Agradeceré mucho a cualquiera que me informe de alguna diferencia que no se detecte o si se encuentran con algún problema en el uso de este procedimiento. (Contacto)

El procedimiento MSSQL

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

Cómo usar el procedimiento

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
 
 

Notas acerca de que hay en cada una de las respuestas posibles

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

Correcciones / Actualizaciones realizadas

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.