Compare databases structure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rafal S. » Compare databases structure

Compare databases structure

Rate Topic

Display Mode

Topic Options

Author

Message

skra

Posted Wednesday, August 20, 2008 1:25 AM

Say Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 4:11 AM
Points: 711, Visits: 210

Comments posted to this topic are about the item Compare databases structure

Post #555567

randal.schmidt

Posted Monday, October 27, 2008 7:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116

I just tried it and it worked great. I used it on two databases where I had made changes (to the test version of the DB) to one. I was very impressed. I have a similar situation with two databases that exist on different servers. Is there a way to use this script to compare two databases with the same name on different servers? Again...nice job!

Post #592074

skra

Posted Monday, October 27, 2008 8:03 AM

Say Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 4:11 AM
Points: 711, Visits: 210

I am afraid that there may be a lot of bugs in the procedure to fix up due to the script complication and I was already told that the way the results are returned is not clear and intuitive but anyway thank You very much for kind words :)

> I have a similar situation with two databases that exist on different servers.
> Is there a way to use this script to compare two databases with the same name on different servers?

I think it would be possible to prepare similar script to compare databases on two different servers:
- one way: via linked servers, the script would have to accept linked server beside database name and modify all queries to use them but I haven't tested this solution yet so I do not know if it can work
- second way: to incorporate OPENROWSET clause in all queries and give extra parameters to the procedure so that You can provide a connection string and provider name
- another way: I have no idea what it would be like but I am sure there exists any

I will try to prepare such script as soon as I can.

Post #592124

randal.schmidt

Posted Monday, October 27, 2008 8:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116

Although the results screen is basic, it is easy to determine the change to schemas in a test versus production settings, for example. It does not have to be intuitive for a DBA to understand it. It does produce a lot of "temp" stuff that may be different between the databases. I noticed a lot of "$" objects showing up. But....

This helped me to see that the vendor application package I am using creates a ton of triggers based on table relationships to keep data in sync. Since I have not keyed in that data in production, the triggers do not exist. So your procedure helped me understand how the vendor DB is opewrating under the hood. An added benefit!

Post #592132

abba-602531

Posted Monday, October 27, 2008 3:02 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, July 24, 2009 2:52 PM
Points: 95, Visits: 457

I thought this code was great. Thank you for sharing. I made one modification, for each query I added a new column, TYPE, so I would know (without thinking about it) what differences I was looking at.

For example, for the table differences I added after the select
''table'' as TYPE,

for the indexes I added
''indexes'' as TYPE

etc.

This is not necessary but makes it quick for me to glance at the data and see where the difference are occurring.

Post #592465

randal.schmidt

Posted Monday, October 27, 2008 3:33 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116

I am not an SQL expert. There are a lot of select statements and it is a complex script. Can you share the changes you made with the table/type in full context?

Post #592485

Veronka

Posted Tuesday, October 28, 2008 6:35 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:17 PM
Points: 52, Visits: 1,189

I love it! I just tried it and it is a BIG starting point for what I need.

Thank you !!!

Post #592800

abba-602531

Posted Wednesday, October 29, 2008 10:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, July 24, 2009 2:52 PM
Points: 95, Visits: 457

Sorry for the delay. I had to take out some company-specific code I had added. Here are the small changes I made. I didn't make the changes to the partition code (the last 2 queries) just because I am not using it but it should be simple enough for you to add.

Thanks again!

/*****************************************************************************************************************************************
*
* 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 ''table'' as TYPE,
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 ''indexes'' as TYPE,
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)

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

set @command = cast('select ''check constraint'' as TYPE,
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 ''FK'' as TYPE,
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 ''other'' as TYPE,
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 ''views/trigs/procs'' as TYPE,
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 ''partition schemes'' as TYPE,
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'

Post #593741

randal.schmidt

Posted Wednesday, October 29, 2008 10:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, October 26, 2012 9:46 AM
Points: 33, Visits: 116

SUPER!!!!! This is a great asset to me. I will be using it a lot to verify I have made changes to my production database that match my development database. Thank you.

Post #593758

prajwal.kumar9

Posted Friday, June 11, 2010 4:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 12, 2011 5:03 AM
Points: 9, Visits: 12

Hi Its indeed a great procedure.even i used it but found some bug in it,like on the procedure,view and function part.

Even after making all the changes in database as suggested by this query, again when i run it it still shows me the difference.

I will tell u on what i did exactly.

1) I ran the query.it suggested me changes in my proc.which was correct and hence altered and created procedures accordingly.

2) Now when i run the query again its still suggests me changes.

It will be grateful if you could help.Relying heavily on this. Undoubtedly great work done. writing this query.

Thanks

Prajwal

Post #935913

« Prev Topic | Next Topic »

Permissions