Bases de datos

SQL Server

Diferencias entre bases de datos

Índice

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: Importante:

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(4000) = null
									) as
 
declare @qry varchar(4000);
 
	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) exec ('insert into #excludeObjects ' + @excludeObjects_SELECT);
 
	-- 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  exc.OBJECT_NAME is null and (b.name is null or  c.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
		where b.object_id is null or c.object_id 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
		where 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_NAMENombre de objeto
OBJECT_TYPETipo de objeto
IN_DB1"X" si existe en DB1
IN_DB2"X" si existe en DB2
[ 2 ] Columnas OBJECT_NAMENombre de objeto
COLUMN_NAMENombre de columna
IN_DB1"X" si existe en DB1
IN_DB2"X" si existe en DB2
[ 3 ] Columnas a detalle TABLE_NAMENombre de tabla
COLUMN_NAMENombre de columna
COLUMN_DEF_DB1En DB1: Definición de columna
N_DB1En DB1: Correlativo de columna
IS_NULLABLE_DB1En DB1: Acepta nulos
COLLATION_NAME_DB1En DB1: Nombre de colación
DF_VALUE_DB1En DB1: Valor por omisión
COLUMN_DEF_DB2En DB2: Definición de columna
N_DB2En DB2: Correlativo de columna
IS_NULLABLE_DB2En DB2: Acepta nulos
COLLATION_NAME_DB2En DB2: Nombre de colación
DF_VALUE_DB2En DB2: Valor por omisión
[ 4 ] Rutinas ROUTINE_NAMENombre de rutina
ROUTINE_TYPETipo de rutina
IN_DB1"X" si existe en DB1
IN_DB2"X" si existe en DB2
[ 5 ] Parámetros por rutina ROUTINE_TYPETipo de rutina
ROUTINE_NAMENombre de rutina
PARAMETER_IDCorrelativo de parámetro
PARAMETER_NAMENombre 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_DB1En DB1: Valor por omisión del parámetro
DEF_IN_DB2En DB2: Valor por omisión del parámetro
[ 6 ] Comparación de existencia de llaves foráneas TABLE_NAMENombre de la tabla
REF_TABLE_NAMENombre de la tabla referenciada
FKNombre de la llave foránea
IN_DB1"X" si existe en DB1
IN_DB2"X" si existe en DB2