Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compare databases structure


Compare databases structure

Author
Message
skra
skra
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 228
Comments posted to this topic are about the item Compare databases structure
randal.schmidt
randal.schmidt
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 120
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!
skra
skra
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 228
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 Smile

> 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.
randal.schmidt
randal.schmidt
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 120
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!
abba-602531
abba-602531
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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.
randal.schmidt
randal.schmidt
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 120
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?
Veronka
Veronka
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 1219
I love it! I just tried it and it is a BIG starting point for what I need.

Thank you !!!
abba-602531
abba-602531
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
   Wink as ra
   cross join
   (
   select * from master.sys.databases where name = @db_b
   Wink 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)
      Wink
   Wink
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)
      Wink
   Wink
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)
      Wink
   Wink
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)
      Wink
   Wink
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
   Wink
   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
   Wink
   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('''')
      Wink
      as tempres(object_body)
   Wink
   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('''')
      Wink
      as tempres(object_body)
   Wink
   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)
         Wink
      Wink
   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
   Wink
   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
   Wink
   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, '''')
         Wink
      Wink
   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   
   Wink
   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   
   Wink
   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))
         Wink
      Wink
   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'
randal.schmidt
randal.schmidt
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 120
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.
prajwal.kumar9
prajwal.kumar9
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search