Compare databases structure

,

This procedure accepts two parameters: names of two databases to compare.

Since it was a little bit arduous to write it what surely made me overlook some obvious mistakes and I am aware of the fact that it may contain some bugs, so I am waiting for feedback about it and I am open for any suggestions of improvement.

/*****************************************************************************************************************************************
 *
 * Author  Rafal Skotak
 * Purpose This procedure is supposed to compare selected databases options and objects and return found differences
 *         (it should compare databases structures)
 * Date    2008.08.19
 *
 ******************************************************************************************************************************************/


if object_id('dbo.proc_compare_databases', 'P') is not null
	drop procedure dbo.proc_compare_databases
go

create procedure dbo.proc_compare_databases
	@db_a sysname,
	@db_b sysname
as
begin
	set nocount on

	if @db_a is null
	begin
		raiserror('Database A name is null', 16, 1)
		return
	end

	if @db_b is null
	begin
		raiserror('Database B name is null', 16, 1)
		return
	end

	if db_id(@db_a) is null
	begin
		raiserror('Database A does not exist', 16, 1)
		return
	end

	if db_id(@db_b) is null
	begin
		raiserror('Database B does not exist', 16, 1)
		return
	end

	declare @command varchar(max)

	------------------------------------------------------------------------------------------------------------------
	-- compare databases options

	select 
		ra.compatibility_level,
		rb.compatibility_level,
		ra.collation_name,
		rb.collation_name,
		ra.user_access,
		rb.user_access,
		ra.user_access_desc,
		rb.user_access_desc,	
		ra.is_read_only,
		rb.is_read_only,
		ra.is_auto_close_on,
		rb.is_auto_close_on,
		ra.is_auto_shrink_on,
		rb.is_auto_shrink_on,
		ra.state,
		rb.state,
		ra.state_desc,
		rb.state_desc,
		ra.is_in_standby	,
		rb.is_in_standby,
		ra.is_supplemental_logging_enabled,
		rb.is_supplemental_logging_enabled,
		ra.snapshot_isolation_state,
		rb.snapshot_isolation_state,
		ra.snapshot_isolation_state_desc	,
		rb.snapshot_isolation_state_desc,
		ra.is_read_committed_snapshot_on,
		rb.is_read_committed_snapshot_on,
		ra.recovery_model,
		rb.recovery_model,
		ra.recovery_model_desc,
		rb.recovery_model_desc,
		ra.is_auto_create_stats_on	,
		rb.is_auto_create_stats_on,
		ra.is_auto_update_stats_on,
		rb.is_auto_update_stats_on,
		ra.is_ansi_null_default_on,
		rb.is_ansi_null_default_on,
		ra.is_ansi_nulls_on,
		rb.is_ansi_nulls_on,
		ra.is_ansi_padding_on,
		rb.is_ansi_padding_on,
		ra.is_ansi_warnings_on,
		rb.is_ansi_warnings_on,
		ra.is_arithabort_on,
		rb.is_arithabort_on,
		ra.is_concat_null_yields_null_on,
		rb.is_concat_null_yields_null_on,
		ra.is_numeric_roundabort_on,
		rb.is_numeric_roundabort_on,
		ra.is_quoted_identifier_on,
		rb.is_quoted_identifier_on,
		ra.is_recursive_triggers_on,
		rb.is_recursive_triggers_on,
		ra.is_cursor_close_on_commit_on,
		rb.is_cursor_close_on_commit_on,
		ra.is_local_cursor_default,
		rb.is_local_cursor_default,
		ra.is_fulltext_enabled,
		rb.is_fulltext_enabled,
		ra.is_broker_enabled,
		rb.is_broker_enabled
	from
	(
	select * from master.sys.databases where name = @db_a
	) as ra
	cross join
	(
	select * from master.sys.databases where name = @db_b
	) as rb
	where
		ra.compatibility_level <> rb.compatibility_level or
		ra.collation_name <> rb.collation_name or
		ra.user_access <> rb.user_access or
		ra.is_read_only <> rb.is_read_only or
		ra.is_auto_close_on <> rb.is_auto_close_on or
		ra.is_broker_enabled <> rb.is_broker_enabled or
		ra.is_fulltext_enabled <> rb.is_fulltext_enabled or
		ra.is_local_cursor_default <> rb.is_local_cursor_default or
		ra.is_cursor_close_on_commit_on <> rb.is_cursor_close_on_commit_on or
		ra.is_recursive_triggers_on <> rb.is_recursive_triggers_on or
		ra.is_quoted_identifier_on <> rb.is_quoted_identifier_on or
		ra.is_numeric_roundabort_on <> rb.is_numeric_roundabort_on or
		ra.is_concat_null_yields_null_on <> rb.is_concat_null_yields_null_on or
		ra.is_arithabort_on <> rb.is_arithabort_on or
		ra.is_ansi_warnings_on <> rb.is_ansi_warnings_on or
		ra.is_ansi_padding_on <> rb.is_ansi_padding_on or
		ra.is_ansi_nulls_on <> rb.is_ansi_nulls_on or
		ra.is_ansi_null_default_on <> rb.is_ansi_null_default_on or
		ra.is_auto_update_stats_on <> rb.is_auto_update_stats_on or
		ra.is_auto_create_stats_on <> rb.is_auto_create_stats_on or
		ra.recovery_model_desc <> rb.recovery_model_desc or
		ra.recovery_model <> rb.recovery_model or
		ra.is_read_committed_snapshot_on <> rb.is_read_committed_snapshot_on or
		ra.snapshot_isolation_state_desc <> rb.snapshot_isolation_state_desc or
		ra.snapshot_isolation_state <> rb.snapshot_isolation_state or
		ra.is_supplemental_logging_enabled <> rb.is_supplemental_logging_enabled or
		ra.is_in_standby <> rb.is_in_standby or
		ra.state <> rb.state or
		ra.is_auto_shrink_on <> rb.is_auto_shrink_on 



	------------------------------------------------------------------------------------------------------------------
	-- compare tables

	set @command = cast('select 
	ra.schema_name,
	rb.schema_name,
	ra.table_name,
	rb.table_name,
	ra.column_name,
	rb.column_name,
	ra.type_name,
	rb.type_name,
	ra.uses_ansi_nulls,
	rb.uses_ansi_nulls,
	ra.column_id,
	rb.column_id,
	ra.system_type_id,
	rb.system_type_id,
	ra.max_length,
	rb.max_length,
	ra.precision,
	rb.precision,	
	ra.scale,
	rb.scale,
	ra.collation_name,
	rb.collation_name,
	ra.is_nullable,
	rb.is_nullable,
	ra.is_ansi_padded,
	rb.is_ansi_padded,
	ra.is_rowguidcol,
	rb.is_rowguidcol,
	ra.is_identity,
	rb.is_identity,
	ra.is_computed,
	rb.is_computed
from
(
select 
	st.object_id,
	ss.name as schema_name,
	st.name as table_name,
	sc.name as column_name,
	sts.name as type_name,
	uses_ansi_nulls,
	column_id,
	sc.system_type_id,
	sc.user_type_id,
	sc.max_length,
	sc.precision,
	sc.scale,
	sc.collation_name,
	sc.is_nullable,
	sc.is_ansi_padded,
	sc.is_rowguidcol,
	sc.is_identity,
	sc.is_computed
from [' as varchar(max)) + @db_a + '].sys.tables st inner join
	[' + @db_a + '].sys.schemas ss on
		st.schema_id = ss.schema_id inner join
	[' + @db_a + '].sys.columns as sc on
		sc.object_id = st.object_id inner join
	[' + @db_a + '].sys.types as sts on
		sc.user_type_id = sts.user_type_id
)
as ra full outer join
(
select 
	st.object_id,
	ss.name as schema_name,
	st.name as table_name,
	sc.name as column_name,
	sts.name as type_name,
	uses_ansi_nulls,
	column_id,
	sc.system_type_id,
	sc.user_type_id,
	sc.max_length,
	sc.precision,
	sc.scale,
	sc.collation_name,
	sc.is_nullable,
	sc.is_ansi_padded,
	sc.is_rowguidcol,
	sc.is_identity,
	sc.is_computed
from 
	[' + @db_b + '].sys.tables st inner join
	[' + @db_b + '].sys.schemas ss on
		st.schema_id = ss.schema_id inner join
	[' + @db_b + '].sys.columns as sc on
		sc.object_id = st.object_id inner join
	[' + @db_b + '].sys.types as sts on
		sc.user_type_id = sts.user_type_id
)
as rb on
	ra.schema_name = rb.schema_name collate database_default and
	ra.table_name = rb.table_name collate database_default and
	ra.column_name = rb.column_name collate database_default
where
	ra.object_id is null or rb.object_id is null or
	(ra.object_id is not null and rb.object_id is not null and
		( 
			(ra.type_name <> rb.type_name collate database_default ) or
			(ra.column_id <> rb.column_id) or
			(ra.uses_ansi_nulls <> rb.uses_ansi_nulls) or
			(ra.system_type_id <> rb.system_type_id) or
			(ra.user_type_id <> rb.user_type_id) or
			(ra.max_length <> rb.max_length) or
			(ra.precision <> rb.precision) or
			(ra.scale <> rb.scale) or
			(ra.is_nullable <> rb.is_nullable) or
			(ra.is_ansi_padded <> rb.is_ansi_padded) or
			(ra.is_rowguidcol <> rb.is_rowguidcol) or
			(ra.is_identity <> rb.is_identity) or
			(ra.is_computed <> rb.is_computed) or
			(ra.collation_name <> rb.collation_name collate database_default)
		)
	)
order by
	ra.schema_name,
	ra.table_name,
	ra.column_name'

	--print @command

	exec(@command)

	------------------------------------------------------------------------------------------------------------------
	-- compare indexes

	set @command = cast('
select 
 	ra.schema_name,
	rb.schema_name,
	ra.table_name,
	rb.table_name,
	ra.index_name,
	rb.index_name,
	ra.index_column_name,
	rb.index_column_name,
	ra.type_name,
	rb.type_name,
	ra.is_primary_key,
	rb.is_primary_key,
	ra.key_ordinal,
	rb.key_ordinal,
	ra.is_descending_key,
	rb.is_descending_key,
	ra.is_included_column,
	rb.is_included_column,
	ra.type,
	rb.type,
	ra.type_desc,	
	rb.type_desc,
	ra.is_unique,
	rb.is_unique,
	ra.ignore_dup_key,
	rb.ignore_dup_key,
	ra.is_unique_constraint,
	rb.is_unique_constraint,
	ra.is_disabled,
	rb.is_disabled,
	ra.system_type_id,
	rb.system_type_id,
	ra.user_type_id,
	rb.user_type_id
from
(
select 
	st.object_id,
	ss.name as schema_name,
	st.name as table_name,
	si.name as index_name,
	sc.name as index_column_name,
	sts.name as type_name,
	is_primary_key,
	key_ordinal,
	is_descending_key,
	is_included_column,
	si.type,
	si.type_desc,
	is_unique,
	ignore_dup_key,
	is_unique_constraint,
	is_disabled,
	sc.system_type_id,
	sc.user_type_id
from 
	['as varchar(max)) + @db_a + '].sys.tables st inner join
	[' + @db_a + '].sys.schemas ss on
		st.schema_id = ss.schema_id inner join
	[' + @db_a + '].sys.columns as sc on
		sc.object_id = st.object_id inner join
	[' + @db_a + '].sys.indexes as si on
		si.object_id = st.object_id inner join
	[' + @db_a + '].sys.index_columns sic on
		si.object_id = sic.object_id and
		si.index_id = sic.index_id and
		sc.column_id = sic.column_id inner join
	[' + @db_a + '].sys.types as sts on
		sc.user_type_id = sts.user_type_id
)
as ra full outer join
(
select 
	st.object_id,
	ss.name as schema_name,
	st.name as table_name,
	si.name as index_name,
	sc.name as index_column_name,
	sts.name as type_name,
	is_primary_key,
	key_ordinal,
	is_descending_key,
	is_included_column,
	si.type,
	si.type_desc,
	is_unique,
	ignore_dup_key,
	is_unique_constraint,
	is_disabled,
	sc.system_type_id,
	sc.user_type_id
from 
	[' + @db_b + '].sys.tables st inner join
	[' + @db_b + '].sys.schemas ss on
		st.schema_id = ss.schema_id inner join
	[' + @db_b + '].sys.columns as sc on
		sc.object_id = st.object_id inner join
	[' + @db_b + '].sys.indexes as si on
		si.object_id = st.object_id inner join
	[' + @db_b + '].sys.index_columns sic on
		si.object_id = sic.object_id and
		si.index_id = sic.index_id and
		sc.column_id = sic.column_id inner join
	[' + @db_b + '].sys.types as sts on
		sc.user_type_id = sts.user_type_id
)
as rb on
	ra.schema_name = rb.schema_name collate database_default and
	ra.table_name = rb.table_name collate database_default and
	ra.index_name = rb.index_name collate database_default and
	ra.index_column_name = rb.index_column_name
where
	ra.object_id is null or rb.object_id is null or
	(ra.object_id is not null and rb.object_id is not null and
		( 
			(ra.is_primary_key <> rb.is_primary_key) or
			(ra.type <> rb.type) or
			(ra.type_desc <> rb.type_desc collate database_default ) or
			(ra.type_name <> rb.type_name collate database_default ) or
			(ra.is_unique <> rb.is_unique) or
			(ra.is_descending_key <> rb.is_descending_key) or
			(ra.key_ordinal <> rb.key_ordinal) or
			(ra.is_included_column <> rb.is_included_column) or
			(ra.ignore_dup_key <> rb.ignore_dup_key) or
			(ra.is_unique_constraint <> rb.is_unique_constraint) or
			(ra.is_disabled <> rb.is_disabled) or
			(ra.system_type_id <> rb.system_type_id) or
			(ra.user_type_id <> rb.user_type_id)
		)
	)
order by
	ra.schema_name,
	ra.table_name,
	ra.is_primary_key desc,
	ra.index_name,
	ra.is_included_column asc,
	ra.key_ordinal asc'

	--print @command

	exec(@command)

	set @command = cast('select 
	ra.schema_name,
	rb.schema_name,
	ra.table_name,
	rb.table_name,
	ra.check_constraint_name,
	rb.check_constraint_name,
	ra.type_desc,	
	rb.type_desc,
	ra.definition,
	rb.definition
from
(
select
	st.object_id,
	ss.name as schema_name,
	st.name as table_name,
	scc.name as check_constraint_name,
	scc.type_desc,
	definition
from 
	['as varchar(max)) + @db_a + '].sys.check_constraints as scc inner join
	[' + @db_a + '].sys.tables as st on
		st.object_id = scc.parent_object_id inner join
	[' + @db_a + '].sys.schemas ss on
		st.schema_id = ss.schema_id
)
as ra full outer join
(
select
	st.object_id,
	ss.name as schema_name,
	st.name as table_name,
	scc.name as check_constraint_name,
	scc.type_desc,
	definition
from 
	[' + @db_b + '].sys.check_constraints as scc inner join
	[' + @db_b + '].sys.tables as st on
		st.object_id = scc.parent_object_id inner join
	[' + @db_b + '].sys.schemas ss on
		st.schema_id = ss.schema_id
)
as rb on
	ra.schema_name = rb.schema_name collate database_default and
	ra.table_name = rb.table_name collate database_default and
	ra.check_constraint_name = rb.check_constraint_name collate database_default
where
	ra.object_id is null or rb.object_id is null or
	(ra.object_id is not null and rb.object_id is not null and
		( 
			(ra.definition <> rb.definition)
		)
	)
order by
	ra.schema_name,
	ra.table_name,	
	ra.check_constraint_name'

	--print @command

	exec (@command)

	---------------------------------------------------------------------------------------------
	-- compare foreign keys

	set @command = cast('select 
	ra.foreign_key_name,
	rb.foreign_key_name,
	ra.type_desc,
	rb.type_desc,
	ra.is_disabled,
	rb.is_disabled,
	ra.delete_referential_action,
	rb.delete_referential_action,
	ra.delete_referential_action_desc,
	rb.delete_referential_action_desc,
	ra.update_referential_action,
	rb.update_referential_action,
	ra.update_referential_action_desc,
	rb.update_referential_action_desc,
	ra.schema_a_name,
	rb.schema_a_name,
	ra.table_a_name,
	rb.table_a_name,
	ra.schema_b_name,
	rb.schema_b_name,
	ra.table_b_name,
	rb.table_b_name,
	ra.column_name,
	rb.column_name,
	ra.ref_column_name,
	rb.ref_column_name
from
(
select 
	sfk.object_id,
	sfk.name as foreign_key_name,
	sfk.type_desc,
	is_disabled,
	delete_referential_action,
	delete_referential_action_desc,
	update_referential_action,
	update_referential_action_desc,
	ss1.name as schema_a_name,
	so1.name as table_a_name,
	ss2.name as schema_b_name,
	so2.name as table_b_name,
	sc1.name as column_name,
	sc2.name as ref_column_name
from 
	[' as varchar(max)) + @db_a + '].sys.foreign_keys as sfk inner join
	[' + @db_a + '].sys.objects as so1 on
		so1.object_id = sfk.parent_object_id inner join
	[' + @db_a + '].sys.objects as so2 on
		so2.object_id = sfk.parent_object_id inner join
	[' + @db_a + '].sys.foreign_key_columns as sfkc on
		sfk.object_id = sfkc.constraint_object_id inner join
	[' + @db_a + '].sys.columns as sc1 on
		sc1.object_id = sfkc.referenced_object_id and
		sc1.column_id = sfkc.parent_column_id inner join
	[' + @db_a + '].sys.columns as sc2 on
		sc2.object_id = sfkc.referenced_object_id and
		sc2.column_id = sfkc.parent_column_id inner join
	[' + @db_a + '].sys.schemas as ss1 on
		so1.schema_id = ss1.schema_id inner join
	[' + @db_a + '].sys.schemas as ss2 on
		so2.schema_id = ss2.schema_id
)
as ra full outer join
(
select 
	sfk.object_id,
	sfk.name as foreign_key_name,
	sfk.type_desc,
	is_disabled,
	delete_referential_action,
	delete_referential_action_desc,
	update_referential_action,
	update_referential_action_desc,
	ss1.name as schema_a_name,
	so1.name as table_a_name,
	ss2.name as schema_b_name,
	so2.name as table_b_name,
	sc1.name as column_name,
	sc2.name as ref_column_name
from 
	[' + @db_b + '].sys.foreign_keys as sfk inner join
	[' + @db_b + '].sys.objects as so1 on
		so1.object_id = sfk.parent_object_id inner join
	[' + @db_b + '].sys.objects as so2 on
		so2.object_id = sfk.parent_object_id inner join
	[' + @db_b + '].sys.foreign_key_columns as sfkc on
		sfk.object_id = sfkc.constraint_object_id inner join
	[' + @db_b + '].sys.columns as sc1 on
		sc1.object_id = sfkc.referenced_object_id and
		sc1.column_id = sfkc.parent_column_id inner join
	[' + @db_b + '].sys.columns as sc2 on
		sc2.object_id = sfkc.referenced_object_id and
		sc2.column_id = sfkc.parent_column_id inner join
	[' + @db_b + '].sys.schemas as ss1 on
		so1.schema_id = ss1.schema_id inner join
	[' + @db_b + '].sys.schemas as ss2 on
		so2.schema_id = ss2.schema_id
)
as rb on
	ra.schema_a_name = rb.schema_a_name collate database_default and
	ra.schema_b_name = rb.schema_b_name collate database_default and
	ra.table_a_name = rb.table_a_name collate database_default and
	ra.table_b_name = rb.table_b_name collate database_default and
	ra.table_a_name = rb.table_a_name collate database_default and
	ra.column_name = rb.ref_column_name collate database_default and
	ra.foreign_key_name = rb.foreign_key_name collate database_default
where
	ra.object_id is null or rb.object_id is null or
	(ra.object_id is not null and rb.object_id is not null and
		( 
			(ra.is_disabled <> rb.is_disabled) or
			(ra.delete_referential_action <> rb.delete_referential_action) or
			(ra.update_referential_action_desc <> rb.update_referential_action_desc)
		)
	)
order by
	ra.foreign_key_name,
	rb.foreign_key_name,
	ra.schema_a_name,
	rb.schema_a_name,
	ra.table_a_name,
	rb.table_a_name,
	ra.schema_b_name,
	rb.schema_b_name,
	ra.table_b_name,
	rb.table_b_name,
	ra.column_name,
	rb.column_name,
	ra.ref_column_name,
	rb.ref_column_name'

	--print @command

	exec (@command)

	---------------------------------------------------------------------------------------------------
	-- find other missing objects

	set @command = cast('select 
		ra.schema_name,
		ra.object_name,
		ra.object_type,
		rb.schema_name,
		rb.object_name,
		rb.object_type
	from
	(
	select 
		ss.name as schema_name,
		so.object_id,
		so.name as object_name,
		so.type as object_type
	from 
		[' as varchar(max)) + @db_a + '].sys.objects as so left outer join
		[' + @db_a + '].sys.schemas as ss on
			so.schema_id = ss.schema_id
	)
	as ra full outer join
	(
	select 
		ss.name as schema_name,
		so.object_id,
		so.name as object_name,
		so.type as object_type
	from 
		[' + @db_b + '].sys.objects as so left outer join
		[' + @db_b + '].sys.schemas as ss on
			so.schema_id = ss.schema_id
	)
	as rb on
		ra.schema_name = rb.schema_name collate database_default and
		ra.object_name = rb.object_name collate database_default and
		ra.object_type = rb.object_type collate database_default
	where
		ra.object_id is null or rb.object_id is null 
	order by
		ra.schema_name,
		rb.schema_name,
		ra.object_name,
		rb.object_name,
		ra.object_type,
		rb.object_type'

	--print @command

	exec (@command)

	-----------------------------------------------------------------------------------------------
	-- compare views, triggers, procedures

	set @command = cast('select 
		ra.schema_name,
		ra.object_name,
		ra.object_type,
		rb.schema_name,
		rb.object_name,
		rb.object_type,
		ra.encrypted,
		rb.encrypted,
		ra.object_body,
		rb.object_body
	from
	(
	select 
		so.object_id,
		so.name as object_name,
		ss.name as schema_name,
		so.type as object_type,
		sc.encrypted,
		object_body
	from 
		[' as varchar(max)) + @db_a + '].sys.objects as so inner join
		[' + @db_a + '].sys.schemas as ss on
			so.schema_id = ss.schema_id inner join
		(select id, min(cast(encrypted as int)) as encrypted from [' + @db_a + '].sys.syscomments group by id) as sc on
			sc.id = so.object_id cross apply
		(
		select 
			replace([text], char(13), '''') as [text()]
		from
			[' + @db_a + '].sys.syscomments as scx
		where
			scx.id = so.object_id
		for xml path('''')
		)
		as tempres(object_body)
	)
	as ra full outer join
	(
	select 
		so.object_id,
		so.name as object_name,
		ss.name as schema_name,
		so.type as object_type,
		sc.encrypted,
		object_body
	from 
		[' + @db_b + '].sys.objects as so inner join
		[' + @db_b + '].sys.schemas as ss on
			so.schema_id = ss.schema_id inner join
		(select id, min(cast(encrypted as int)) as encrypted from [' + @db_b + '].sys.syscomments group by id) as sc on
			sc.id = so.object_id cross apply
		(
		select 
			replace([text], char(13), '''') as [text()]
		from
			[' + @db_b + '].sys.syscomments as scx
		where
			scx.id = so.object_id
		for xml path('''')
		)
		as tempres(object_body)
	)
	as rb on
		ra.schema_name = rb.schema_name collate database_default and
		ra.object_name = rb.object_name collate database_default and
		ra.object_type = rb.object_type collate database_default
	where
		ra.object_id is null or rb.object_id is null or
		ra.schema_name is null or rb.schema_name is null or
		ra.object_name is null or rb.object_name is null or
		ra.object_type is null or rb.object_type is null or
		(	
			ra.object_id   is not null and rb.object_id   is not null and
			ra.schema_name is not null and rb.schema_name is not null and
			ra.object_name is not null and rb.object_name is not null and
			ra.object_type is not null and rb.object_type is not null and
			( 
				(ra.encrypted <> rb.encrypted) or
				(ra.object_body <> rb.object_body)
			)
		)
	order by
		ra.object_type,
		rb.object_type,
		ra.object_name,
		ra.schema_name,
		rb.object_name,
		rb.schema_name,
		ra.encrypted,
		rb.encrypted,
		ra.object_body,
		rb.object_body'

	--print @command

	exec (@command)

	----------------------------------------------------------------------------------------
	-- compare partition schemes

	set @command = cast('select 
		ra.partition_scheme_name,
		ra.scheme_type,
		ra.is_default,
		ra.function_name,
		ra.function_type,
		ra.fanout,
		ra.boundary_value_on_right,
		ra.system_type_id,
		ra.parameter_type_name,
		ra.max_length,
		ra.precision,
		ra.scale,
		ra.collation_name,	
		rb.partition_scheme_name,
		rb.scheme_type,
		rb.is_default,
		rb.function_name,
		rb.function_type,
		rb.fanout,
		rb.boundary_value_on_right,
		rb.system_type_id,
		rb.parameter_type_name,
		rb.max_length,
		rb.precision,
		rb.scale,
		rb.collation_name	
	from
	(
		select 
			ps.name as partition_scheme_name,
			ps.type as scheme_type,
			ps.is_default,
			pf.name as function_name,
			pf.type as function_type,
			pf.fanout,
			pf.boundary_value_on_right,
			st.system_type_id,
			st.name as parameter_type_name,
			pp.max_length,
			pp.precision,
			pp.scale,
			pp.collation_name	
		from 
			[' as varchar(max)) + @db_a + '].sys.partition_schemes as ps left outer join
			[' + @db_a + '].sys.partition_functions as pf on
				ps.function_id = pf.function_id left outer join
			[' + @db_a + '].sys.partition_parameters as pp on
				pf.function_id = pp.function_id left outer join
			[' + @db_a + '].sys.types as st on
				pp.system_type_id = st.system_type_id
	)
	as ra full outer join
	(
		select 
			ps.name as partition_scheme_name,
			ps.type as scheme_type,
			ps.is_default,
			pf.name as function_name,
			pf.type as function_type,
			pf.fanout,
			pf.boundary_value_on_right,
			st.system_type_id,
			st.name as parameter_type_name,
			pp.max_length,
			pp.precision,
			pp.scale,
			pp.collation_name	
		from 
			[' + @db_b + '].sys.partition_schemes as ps left outer join
			[' + @db_b + '].sys.partition_functions as pf on
				ps.function_id = pf.function_id left outer join
			[' + @db_b + '].sys.partition_parameters as pp on
				pf.function_id = pp.function_id left outer join
			[' + @db_b + '].sys.types as st on
				pp.system_type_id = st.system_type_id
	)
	as rb on
		ra.partition_scheme_name = rb.partition_scheme_name collate database_default
	where
		ra.partition_scheme_name is null or rb.partition_scheme_name is null or
		(ra.partition_scheme_name is not null and rb.partition_scheme_name is not null and
			( 
				ra.scheme_type <> rb.scheme_type or
				ra.is_default <> rb.is_default or
				(coalesce(cast(ra.is_default as int), -1) <> coalesce(cast(rb.is_default as int), -1)) or
				coalesce(ra.function_name, '''') <> coalesce(rb.function_name, '''') or
				coalesce(ra.function_type, '''') <> coalesce(rb.function_type, '''') or
				(coalesce(cast(ra.fanout as int), -1) <> coalesce(cast(rb.fanout as int), -1)) or
				(coalesce(cast(ra.boundary_value_on_right as int), -1) <> coalesce(cast(rb.boundary_value_on_right as int), -1)) or 
				coalesce(ra.system_type_id, -1) <> coalesce(rb.system_type_id, -1) or
				coalesce(ra.parameter_type_name, '''') <> coalesce(rb.parameter_type_name, '''') or
				coalesce(ra.max_length, -1) <> coalesce(rb.max_length, -1) or	
				coalesce(ra.precision, -1) <> coalesce(rb.precision, -1) or	
				coalesce(ra.scale, -1) <> coalesce(rb.scale, -1) or
				coalesce(ra.collation_name, '''') <> coalesce(rb.collation_name, '''')
			)
		)
	order by
		ra.partition_scheme_name,
		rb.partition_scheme_name,
		ra.function_name,
		rb.function_name'	

	--print @command

	exec (@command)

	------------------------------------------------------------------------------------------------
	-- compare partition schemes

	set @command = cast('select 
		ra.schema_name,
		ra.table_name,
		ra.index_name,
		ra.partition_number, 

		rb.schema_name,
		rb.table_name,
		rb.index_name,
		rb.partition_number, 

		ra.function_name,
		rb.function_name,

		ra.partition_scheme_name,
		rb.partition_scheme_name,
		ra.index_id,
		rb.index_id,

		ra.file_group_name,
		rb.file_group_name,

		ra.value,
		rb.value
	from
	(
		select	
			ss.name as schema_name,
			st.name as table_name,
			spf.name as function_name,
			sps.name as partition_scheme_name,
			si.index_id,
			si.name as index_name,
			partition_number as partition_number, 
			sfg.name as file_group_name,
			sprv.value
		from
			[' as varchar(max)) + @db_a + '].sys.tables as st inner join
			[' + @db_a + '].sys.schemas as ss on
				st.schema_id = ss.schema_id inner join
			[' + @db_a + '].sys.indexes as si on
				st.object_id = si.object_id inner join
			[' + @db_a + '].sys.partitions as sp on
				sp.object_id = st.object_id and	
				si.index_id = sp.index_id inner join
			[' + @db_a + '].sys.allocation_units as sau on
				sau.container_id = sp.partition_id inner join
			[' + @db_a + '].sys.filegroups as sfg on
				sfg.data_space_id = sau.data_space_id inner join
			[' + @db_a + '].sys.partition_schemes as sps on
				sps.data_space_id = si.data_space_id inner join
			[' + @db_a + '].sys.partition_functions as spf on
				spf.function_id = sps.function_id left outer join
			[' + @db_a + '].sys.partition_range_values as sprv on 
				sprv.function_id = spf.function_id and
				partition_number = sprv.boundary_id	
	)
	as ra full outer join
	(
		select	
			ss.name as schema_name,
			st.name as table_name,
			spf.name as function_name,
			sps.name as partition_scheme_name,
			si.index_id,
			si.name as index_name,
			partition_number as partition_number, 
			sfg.name as file_group_name,
			sprv.value
		from
			[' + @db_b + '].sys.tables as st inner join
			[' + @db_b + '].sys.schemas as ss on
				st.schema_id = ss.schema_id inner join
			[' + @db_b + '].sys.indexes as si on
				st.object_id = si.object_id inner join
			[' + @db_b + '].sys.partitions as sp on
				sp.object_id = st.object_id and	
				si.index_id = sp.index_id inner join
			[' + @db_b + '].sys.allocation_units as sau on
				sau.container_id = sp.partition_id inner join
			[' + @db_b + '].sys.filegroups as sfg on
				sfg.data_space_id = sau.data_space_id inner join
			[' + @db_b + '].sys.partition_schemes as sps on
				sps.data_space_id = si.data_space_id inner join
			[' + @db_b + '].sys.partition_functions as spf on
				spf.function_id = sps.function_id left outer join
			[' + @db_b + '].sys.partition_range_values as sprv on 
				sprv.function_id = spf.function_id and
				partition_number = sprv.boundary_id	
	)
	as rb on
		ra.partition_scheme_name = rb.partition_scheme_name collate database_default and
		ra.schema_name = rb.schema_name collate database_default and
		ra.table_name = rb.table_name collate database_default and
		ra.index_name = rb.index_name collate database_default and
		ra.partition_number = rb.partition_number
	where
		ra.partition_scheme_name is null or rb.partition_scheme_name is null or
		ra.table_name            is null or rb.table_name            is null or
		ra.index_name            is null or rb.index_name            is null or
		ra.schema_name           is null or rb.schema_name           is null or
		ra.partition_number      is null or rb.partition_number      is null or
		ra.file_group_name       is null or rb.file_group_name       is null or
		(
			ra.partition_scheme_name is not null and rb.partition_scheme_name is not null and
			ra.table_name            is not null and rb.table_name            is not null and
			ra.index_name            is not null and rb.index_name            is not null and
			ra.schema_name           is not null and rb.schema_name           is not null and
			ra.partition_number      is not null and rb.partition_number      is not null and
			ra.file_group_name       is not null and rb.file_group_name       is not null and
			(
				coalesce(ra.function_name, '') <> coalesce(rb.function_name, '') collate database_default or
				ra.file_group_name <> rb.file_group_name collate database_default or
				cast(ra.value as varchar(max)) <> cast(rb.value as varchar(max))
			)
		)
	order by
		ra.partition_scheme_name,
		rb.partition_scheme_name,
		ra.schema_name,
		rb.schema_name,
		ra.table_name,
		rb.table_name,
		ra.index_name,
		rb.index_name'

end
go

exec dbo.proc_compare_databases 'AdventureWorks', 'AdventureWorks_Old'

Rate

4.22 (9)

Share

Share

Rate

4.22 (9)