Comparing two tables

  • Hi all,

    the following table is used by a third-party product. I have it in two db's TEST and PROD. For obvious reasons counting the spaces in the defaults is bean counting. What is the smartest way to check if they are identical?

     
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sap_sst_ausgabe]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[sap_sst_ausgabe]
    GO

    CREATE TABLE [dbo].[sap_sst_ausgabe] (
    [sap_sst_id] [int] IDENTITY (1, 1) NOT NULL ,
    [bgr00_stype] [char] (1) NOT NULL ,
    [bkpf_stype] [char] (1) NOT NULL ,
    [bseg_stype] [char] (1) NOT NULL ,
    [bgr00_group] [char] (12) NOT NULL ,
    [bgr00_mandt] [char] (3) NOT NULL ,
    [bgr00_usnam] [char] (12) NOT NULL ,
    [bgr00_start] [char] (8) NOT NULL ,
    [bgr00_xkeep] [char] (1) NOT NULL ,
    [bkpf_tcode] [char] (20) NOT NULL ,
    [bkpf_bldata] [char] (8) NOT NULL ,
    [bkpf_blart] [char] (2) NOT NULL ,
    [bkpf_bukrs] [char] (4) NOT NULL ,
    [bkpf_budata] [char] (8) NOT NULL ,
    [bkpf_monat] [char] (2) NOT NULL ,
    [bkpf_waers] [char] (5) NOT NULL ,
    [bkpf_xblnr] [char] (16) NOT NULL ,
    [bkpf_bktxt] [char] (25) NOT NULL ,
    [bseg_tbnam] [char] (30) NOT NULL ,
    [bseg_newbs] [char] (2) NOT NULL ,
    [bseg_newbk] [char] (4) NOT NULL ,
    [bseg_wrbtr] [char] (16) NOT NULL ,
    [bseg_sgtxt] [char] (50) NOT NULL ,
    [bseg_newko] [char] (17) NOT NULL ,
    [bseg_zuonr] [char] (18) NOT NULL ,
    [f1] [char] (1) NOT NULL ,
    [f2] [char] (28) NOT NULL ,
    [f3] [char] (16) NOT NULL ,
    [f4] [char] (97) NOT NULL ,
    [f5] [char] (11) NOT NULL ,
    [f6] [char] (235) NOT NULL ,
    [f6a] [char] (4) NOT NULL ,
    [f7] [char] (337) NOT NULL ,
    [f8] [char] (35) NOT NULL ,
    [f9] [char] (35) NOT NULL ,
    [f10] [char] (35) NOT NULL ,
    [f11] [char] (35) NOT NULL ,
    [f12] [char] (35) NOT NULL ,
    [f13] [char] (35) NOT NULL ,
    [f14] [char] (140) NOT NULL ,
    [f15] [char] (381) NOT NULL ,
    [f16] [char] (30) NOT NULL ,
    [f17] [char] (30) NOT NULL ,
    [f18] [char] (30) NOT NULL ,
    [f19] [char] (30) NOT NULL ,
    [f20] [char] (60) NOT NULL ,
    [f21] [char] (60) NOT NULL ,
    [f22] [char] (413) NOT NULL ,
    [f23] [char] (30) NOT NULL ,
    [f24] [char] (200) NOT NULL ,
    [f25] [char] (30) NOT NULL ,
    [f26] [char] (31) NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[sap_sst_ausgabe] WITH NOCHECK ADD
    CONSTRAINT [DF_sap_sst_ausgabe_bgr00_stype] DEFAULT ('0') FOR [bgr00_stype],
    CONSTRAINT [DF_sap_sst_ausgabe_bkpf_stype] DEFAULT ('1') FOR [bkpf_stype],
    CONSTRAINT [DF_sap_sst_ausgabe_bseg_stype] DEFAULT ('2') FOR [bseg_stype],
    CONSTRAINT [DF_sap_sst_ausgabe_bgr00_group] DEFAULT ('PylonFonds ') FOR [bgr00_group],
    CONSTRAINT [DF_sap_sst_ausgabe_bgr00_mandt] DEFAULT ('010') FOR [bgr00_mandt],
    CONSTRAINT [DF_sap_sst_ausgabe_bgr00_start] DEFAULT ('00000000') FOR [bgr00_start],
    CONSTRAINT [DF_sap_sst_ausgabe_bgr00_xkeep] DEFAULT ('X') FOR [bgr00_xkeep],
    CONSTRAINT [DF_sap_sst_ausgabe_bkpf_tcode] DEFAULT ('FB01') FOR [bkpf_tcode],
    CONSTRAINT [DF_sap_sst_ausgabe_bkpf_blart] DEFAULT ('EK') FOR [bkpf_blart],
    CONSTRAINT [DF_sap_sst_ausgabe_bkpf_waers] DEFAULT ('EUR ') FOR [bkpf_waers],
    CONSTRAINT [DF_sap_sst_ausgabe_bseg_tbnam] DEFAULT ('BBSEG') FOR [bseg_tbnam],
    CONSTRAINT [DF_sap_sst_ausgabe_f1] DEFAULT ('/') FOR [f1],
    CONSTRAINT [DF_sap_sst_ausgabe_f2] DEFAULT ('/ / / ') FOR [f2],
    CONSTRAINT [DF_sap_sst_ausgabe_f3] DEFAULT ('/ ') FOR [f3],
    CONSTRAINT [DF_sap_sst_ausgabe_f4] DEFAULT ('/ / / // / / / / / / /') FOR [f4],
    CONSTRAINT [DF_sap_sst_ausgabe_f5] DEFAULT ('/ /') FOR [f5],
    CONSTRAINT [DF_sap_sst_ausgabe_f6] DEFAULT ('/ / / / // / / / / / / / / / / / / / / / / / / / / ') FOR [f6],
    CONSTRAINT [DF_sap_sst_ausgabe_f6a] DEFAULT ('/ /') FOR [f6a],
    CONSTRAINT [DF_sap_sst_ausgabe_f7] DEFAULT ('/ / / / / / / / / / / / // / / // // / / / / / / / / / / / / / / /// / / / / / / // / / // /') FOR [f7],
    CONSTRAINT [DF_sap_sst_ausgabe_f8] DEFAULT ('/ ') FOR [f8],
    CONSTRAINT [DF_sap_sst_ausgabe_f9] DEFAULT ('/ ') FOR [f9],
    CONSTRAINT [DF_sap_sst_ausgabe_f10] DEFAULT ('/ ') FOR [f10],
    CONSTRAINT [DF_sap_sst_ausgabe_f11] DEFAULT ('/ ') FOR [f11],
    CONSTRAINT [DF_sap_sst_ausgabe_f12] DEFAULT ('/ ') FOR [f12],
    CONSTRAINT [DF_sap_sst_ausgabe_f13] DEFAULT ('/ ') FOR [f13],
    CONSTRAINT [DF_sap_sst_ausgabe_f14] DEFAULT ('/ / / / / / / / / / // / / /// / //') FOR [f14],
    CONSTRAINT [DF_sap_sst_ausgabe_f15] DEFAULT ('/ / / / / // / / / / / / / / / / / / / // / / / / / / / / / / / / / / / / / / / / / / / // / / //') FOR [f15],
    CONSTRAINT [DF_sap_sst_ausgabe_f16] DEFAULT ('/ ') FOR [f16],
    CONSTRAINT [DF_sap_sst_ausgabe] DEFAULT ('/ ') FOR [f17],
    CONSTRAINT [DF_sap_sst_ausgabe_f18] DEFAULT ('/ ') FOR [f18],
    CONSTRAINT [DF_sap_sst_ausgabe_f19] DEFAULT ('/ ') FOR [f19],
    CONSTRAINT [DF_sap_sst_ausgabe_f20] DEFAULT ('/ ') FOR [f20],
    CONSTRAINT [DF_sap_sst_ausgabe_f21] DEFAULT ('/ ') FOR [f21],
    CONSTRAINT [DF_sap_sst_ausgabe_f22] DEFAULT ('/ / / / /// // / / / / / / / / / / / / / / / // / / / / / / / / / / / / / / / / / / / / / / / / / / // / // / / / / // / / ') FOR [f22],
    CONSTRAINT [DF_sap_sst_ausgabe_f23] DEFAULT ('/ ') FOR [f23],
    CONSTRAINT [DF_sap_sst_ausgabe_f24] DEFAULT ('/ / / / / / / / / / / / / / / / / / / / / / / / ') FOR [f24],
    CONSTRAINT [DF_sap_sst_ausgabe_f25] DEFAULT ('/ ') FOR [f25],
    CONSTRAINT [DF_sap_sst_ausgabe_f26] DEFAULT ('/ ') FOR [f26],
    CONSTRAINT [PK_sap_sst_ausgabe] PRIMARY KEY NONCLUSTERED
    (
    [sap_sst_id]
    ) ON [PRIMARY]
    GO

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I would say download a demo version of SQLCompare (www.red-gate.com) or AdeptSQLDiff (www.adeptsql.com). Both do the job nice and easy.

    [font="Verdana"]Markus Bohse[/font]

  • Hi Frank,

    This script is what I use to compare the data in two different tables...worked out fine for me...

    http://www.sqlservercentral.com/scripts/contributions/458.asp

  • Hello winash,

    quote:


    This script is what I use to compare the data in two different tables...worked out fine for me...

    http://www.sqlservercentral.com/scripts/contributions/458.asp


    look good, but how do I get it to work?

    sp_Compare2Tables 'FVS.DB.sap_sst_ausgabe', 'FVS_TEST.DBO.sap_sst_ausgabe',1,0

    results in

    There is no database named FVS. Termination of Procedure.

    I can create in any db I like, right?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • hmmmm....always worked fine for me...just rechecked it and it worked - what I used was

    sp_Compare2Tables 'WADemo.wa.TabCompare1','WALive.wa.TabCompare2',1,0

    and the stored proc is created in WADemo database...

    <Debug mode on>

    the script uses PARSENAME to get the server,DB,schema and table names....and the error you specified occurs in the following block of code in the SP :

    set @SQLStr = 'Select name into #TempTable FROM ['+@SvrName1+'].master.dbo.sysdatabases where name ='''+ @DBName1+''''

    EXECUTE sp_ExecuteSQL @SQLStr

    IF @@rowcount=0

    BEGIN

    PRINT 'There is no database named '+@DBName1+'. Termination of Procedure.'

    RETURN

    END

    (and a similar block for @DBName2)

    so I guess there is some problem with either the input data provided or with the permissions????

    I usually first just run the script with added print statements to check what is happening and then make it into an SP...I think putting in a Print (@SqlStr) in the above code blocks should help trap the problem...

    <Debug mode off>

  • Please check the syntax and input data of the command.

    Hope you have full rights.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • Please check the syntax and input data of the command.

    Hope you have full rights.

    Preethi

    (G.R.Preethiviraj Kulasingham)

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • use tempdb

    if exists (select name from sysobjects where name='sp_list_schema' and xtype='P')

    drop procedure sp_list_schema

    GO

    create procedure sp_list_schema(@db_nameX sysname , @db_Server sysname)

    as

    declare @SQLString as nvarchar (4000)

    declare @db_name as sysname

    declare @db_NameY as sysname

    set @db_name = @db_Server +'.'+@db_nameX

    set @db_nameY=@db_Server +@db_nameX

    set @SQLString = 'if exists (select name from sysobjects where name= '

    set @SQLString =@SQLString + char(39) + 'schema_' +char(39)+ '+ ' + char(39)+@db_namey +char(39)+ ' and xtype='+ char(39) +'U' + char(39) +') '

    set @SQLString =@SQLString + ' drop table schema_' + @db_namey

    exec sp_executesql @SQLString

    --print 'Table Drop ' + 'schema_' + @db_namey

    --print @SQLString

    set @SQLString = 'Select '

    set @SQLString = @SQLString + 'getdate()' + ' time , '

    set @SQLString = @SQLString + char(39) + @db_Server + char(39)+ ' server , '

    set @SQLString = @SQLString + char(39) + @db_name + char(39)+ ' db , '

    set @SQLString = @SQLString + 'obj.name tab , '

    set @SQLString = @SQLString + 'cols.name col , '

    set @SQLString = @SQLString + 'cols.colid , '

    set @SQLString = @SQLString + 'typs.name xtype , '

    set @SQLString = @SQLString + 'cols.length , '

    set @SQLString = @SQLString + 'Case cols.status & 0x08 when 0x08 then ' + char(39) +'X' + char(39) +' else ' + char(39)+char(39) +' end nullable , '

    set @SQLString = @SQLString + 'Case cols.status & 0x80 when 0x80 then ' + char(39) +'X' + char(39) +' else ' + char(39)+char(39) +' end ident , '

    set @SQLString = @SQLString + 'IsNull (cols.prec , '+ char(39)+char(39) +') prec , '

    set @SQLString = @SQLString + 'IsNull (cols.scale , '+ char(39)+char(39) +') scale , '

    set @SQLString = @SQLString + 'IsNull (cmnts.text , '+ char(39)+char(39) +') dflt , '

    set @SQLString = @SQLString + 'Case cols.iscomputed when 1 then ' + char(39) +'X' + char(39) +' else ' + char(39)+char(39) +' end computed '

    set @SQLString = @SQLString + 'into tempdb.dbo.schema_'+ @db_namey + ' '

    set @SQLString = @SQLString + 'From '

    set @SQLString = @SQLString + @db_name + '.dbo.sysobjects as obj , '

    set @SQLString = @SQLString + @db_name + '.dbo.syscolumns as cols , '

    set @SQLString = @SQLString + @db_name + '.dbo.systypes as typs , '

    set @SQLString = @SQLString + @db_name + '.dbo.syscomments as cmnts '

    set @SQLString = @SQLString + 'Where '

    set @SQLString = @SQLString + 'cols.xtype=typs.xtype and '

    set @SQLString = @SQLString + 'cols.xusertype=typs.xusertype and '

    set @SQLString = @SQLString + 'cols.id=obj.id and '

    set @SQLString = @SQLString + 'obj.xtype=' + char(39) +'U' +char(39) + ' and '

    set @SQLString = @SQLString + 'cols.cdefault*=cmnts.id and '

    set @SQLString = @SQLString + 'cmnts.colid=1'

    exec sp_executesql @SQLString

    --print 'Insert InTo Schema ' +@db_namey

    --print @SQLString

    set @SQLString = 'if exists (select name from sysobjects where name= '

    set @SQLString =@SQLString + char(39) + 'schind_' +char(39)+ '+ ' + char(39)+@db_namey +char(39)+ ' and xtype='+ char(39) +'U' + char(39) +') '

    set @SQLString =@SQLString + ' drop table schind_' + @db_namey

    exec sp_executesql @SQLString

    --print 'Table Drop ' + 'schind_' + @db_namey

    --print @SQLString

    set @SQLString = 'Select '

    set @SQLString = @SQLString + 'getdate()' + ' time , '

    set @SQLString = @SQLString + char(39) + @db_Server + char(39)+ ' server , '

    set @SQLString = @SQLString + char(39) + @db_name + char(39)+ ' db , '

    set @SQLString = @SQLString + 'obj.name tab , '

    set @SQLString = @SQLString + 'indx.name ind , '

    set @SQLString = @SQLString + 'indxky.keyno , '

    set @SQLString = @SQLString + 'cols.name KeyName ,'

    set @SQLString = @SQLString + 'case indx.status & 2 when 2 then ' + char(39) +'X' + char(39) +' else ' + char(39)+char(39) +' end uniq , '

    set @SQLString = @SQLString + 'case indx.status & 2048 when 2048 then ' + char(39) +'X' + char(39) +' else ' + char(39)+char(39) +' end pri , '

    set @SQLString = @SQLString + 'case indx.indid when 1 then ' + char(39) +'X' + char(39) +' else ' + char(39)+char(39) +' end clust , '

    set @SQLString = @SQLString + 'indx.soid , '

    set @SQLString = @SQLString + 'indx.csid '

    set @SQLString = @SQLString + 'Into tempdb.dbo.schind_' + @db_namey + ' '

    set @SQLString = @SQLString + 'from '

    set @SQLString = @SQLString + @db_name +'.dbo.sysobjects as obj , '

    set @SQLString = @SQLString + @db_name + '.dbo.sysindexes as indx , '

    set @SQLString = @SQLString + @db_name + '.dbo.sysindexkeys as indxky , '

    set @SQLString = @SQLString + @db_name + '.dbo.syscolumns as cols '

    set @SQLString = @SQLString + ' Where '

    set @SQLString = @SQLString + 'indx.indid not in (0,255) and '

    set @SQLString = @SQLString + 'indx.name not like ' + char(39) +'_WA%' + char(39) +' and '

    set @SQLString = @SQLString + 'first <>0 and '

    set @SQLString = @SQLString + 'indx.id=obj.id and '

    set @SQLString = @SQLString + 'obj.xtype='+char(39)+'U' +char(39) + ' and '

    set @SQLString = @SQLString + 'indxky.id=indx.id' + ' and '

    set @SQLString = @SQLString + 'indxky.indid=indx.indid' + ' and '

    set @SQLString = @SQLString + 'indxky.colid=cols.colid' + ' and '

    set @SQLString = @SQLString + 'indxky.id=cols.id'

    --print @sqlstring

    exec sp_executesql @SQLString

    --print 'Insert InTo Schema ' +@db_namey

    --print @SQLString

    go

    --compare_schema

    if exists (select name from sysobjects where name='compare_schema' and xtype='P')

    drop procedure compare_schema

    go

    create procedure compare_schema(@db1_name sysname, @db2_name sysname)

    as

    declare @db1 sysname,@db2 sysname

    set @db1='schema_'+@db1_name

    set @db2='schema_'+@db2_name

    set nocount on

    exec ('if not exists(select * from sysindexes where object_name(id)="'+@db1+'" and name="ix")'+

    ' create index ix on '+@db1+'(tab,col)')

    exec ('if not exists(select * from sysindexes where object_name(id)="'+@db2+'" and name="ix")'+

    ' create index ix on '+@db2+'(tab,col)')

    exec ('select distinct "new tables",server,db,tab from '+@db1+' db1'+

    ' where not exists (select * from '+@db2+' db2'+

    ' where db1.tab=db2.tab)'+

    'union '+

    'select distinct "new tables",server,db,tab from '+@db2+' db2'+

    ' where not exists (select * from '+@db1+' db1'+

    ' where db1.tab=db2.tab)'+

    'order by server,db,tab')

    exec ('select distinct "new columns",server,db,tab,col from '+@db1+' db1'+

    ' where not exists (select * from '+@db2+' db2'+

    ' where db1.tab=db2.tab'+

    ' and db1.col=db2.col)'+

    'and exists (select * from '+@db2+' db2'+

    ' where db1.tab=db2.tab) '+

    'union '+

    'select distinct "new columns",server,db,tab,col from '+@db2+' db2'+

    ' where not exists (select * from '+@db1+' db1'+

    ' where db1.tab=db2.tab'+

    ' and db1.col=db2.col)'+

    'and exists (select * from '+@db1+' db1'+

    ' where db1.tab=db2.tab) '+

    'order by server,db,tab,col')

    exec ('select "changed columns",server,db,tab,col,colid,xtype,length,nullable,ident,prec,scale,dflt from '+@db1+' db1'+

    ' where exists (select * from '+@db2+' db2'+

    ' where db1.tab=db2.tab '+

    ' and db1.col=db2.col '+

    ' and (db1.xtype<>db2.xtype '+

    ' or db1.length<>db2.length '+

    ' or db1.nullable<>db2.nullable '+

    ' or db1.ident<>db2.ident '+

    ' or db1.prec<>db2.prec '+

    ' or db1.scale<>db2.scale '+

    ' or db1.dflt<>db2.dflt '+

    ' or db1.computed<>db2.computed)) '+

    'union '+

    'select "changed columns",server,db,tab,col,colid,xtype,length,nullable,ident,prec,scale,dflt from '+@db2+' db2'+

    ' where exists (select * from '+@db1+' db1'+

    ' where db1.tab=db2.tab '+

    ' and db1.col=db2.col '+

    ' and (db1.xtype<>db2.xtype '+

    ' or db1.length<>db2.length '+

    ' or db1.nullable<>db2.nullable '+

    ' or db1.ident<>db2.ident '+

    ' or db1.prec<>db2.prec '+

    ' or db1.scale<>db2.scale '+

    ' or db1.dflt<>db2.dflt '+

    ' or db1.computed<>db2.computed)) '+

    'order by tab,col,server,db')

    exec ('select "changed columns colid",server,db,tab,col,colid,xtype,length,nullable,ident,prec,scale,dflt from '+@db1+' db1'+

    ' where exists (select * from '+@db2+' db2'+

    ' where db1.tab=db2.tab '+

    ' and db1.col=db2.col '+

    ' and (db1.colid<>db2.colid '+

    ' or db1.xtype<>db2.xtype '+

    ' or db1.length<>db2.length '+

    ' or db1.nullable<>db2.nullable '+

    ' or db1.ident<>db2.ident '+

    ' or db1.prec<>db2.prec '+

    ' or db1.scale<>db2.scale '+

    ' or db1.dflt<>db2.dflt '+

    ' or db1.computed<>db2.computed)) '+

    'union '+

    'select "changed columns colid",server,db,tab,col,colid,xtype,length,nullable,ident,prec,scale,dflt from '+@db2+' db2'+

    ' where exists (select * from '+@db1+' db1'+

    ' where db1.tab=db2.tab '+

    ' and db1.col=db2.col '+

    ' and (db1.colid<>db2.colid '+

    ' or db1.xtype<>db2.xtype '+

    ' or db1.length<>db2.length '+

    ' or db1.nullable<>db2.nullable '+

    ' or db1.ident<>db2.ident '+

    ' or db1.prec<>db2.prec '+

    ' or db1.scale<>db2.scale '+

    ' or db1.dflt<>db2.dflt '+

    ' or db1.computed<>db2.computed)) '+

    'order by tab,col,server,db')

    set @db1='schind_'+@db1_name

    set @db2='schind_'+@db2_name

    exec ('if not exists(select * from sysindexes where object_name(id)="'+@db1+'" and name="ix")'+

    ' create index ix on '+@db1+'(tab,ind,keyno)')

    exec ('if not exists(select * from sysindexes where object_name(id)="'+@db2+'" and name="ix")'+

    ' create index ix on '+@db2+'(tab,ind,keyno)')

    exec('select distinct "all new indexes",server,db,tab,ind,uniq,pri,clust,soid,csid from '+@db1+' db1'+

    ' where not exists (select * from '+@db2+' db2'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind)'+

    'union '+

    'select distinct "all new indexes",server,db,tab,ind,uniq,pri,clust,soid,csid from '+@db2+' db2'+

    ' where not exists (select * from '+@db1+' db1'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind)'+

    'order by server,db,tab,ind')

    exec('select distinct "all new indexes with keys",server,db,tab,ind,keyno,keyname from '+@db1+' db1'+

    ' where not exists (select * from '+@db2+' db2'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind)'+

    'union '+

    'select distinct "all new indexes with keys",server,db,tab,ind,keyno,keyname from '+@db2+' db2'+

    ' where not exists (select * from '+@db1+' db1'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind)'+

    'order by server,db,tab,ind,keyno')

    exec('select distinct "new indexes",server,db,tab,ind,uniq,pri,clust,soid,csid from '+@db1+' db1'+

    ' where not exists (select * from '+@db2+' db2'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind)'+

    ' and exists (select * from '+@db2+' db2'+

    ' where db2.tab=db1.tab)'+

    'union '+

    'select distinct "new indexes",server,db,tab,ind,uniq,pri,clust,soid,csid from '+@db2+' db2'+

    ' where not exists (select * from '+@db1+' db1'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind)'+

    ' and exists (select * from '+@db1+' db1'+

    ' where db2.tab=db1.tab)'+

    'order by server,db,tab,ind')

    exec('select "new indexes with keys",server,db,tab,ind,keyno,keyname from '+@db1+' db1'+

    ' where not exists (select * from '+@db2+' db2'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind)'+

    ' and exists (select * from '+'schema_'+@db2_name+' db2'+

    ' where db2.tab=db1.tab)'+

    'union '+

    'select "new indexes with keys",server,db,tab,ind,keyno,keyname from '+@db2+' db2'+

    ' where not exists (select * from '+@db1+' db1'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind)'+

    ' and exists (select * from '+'schema_'+@db1_name+' db1'+

    ' where db2.tab=db1.tab)'+

    'order by server,db,tab,ind,keyno')

    exec('select distinct "changed index attributes",server,db,tab,ind,uniq,pri,clust,soid,csid from '+@db1+' db1'+

    ' where exists (select * from '+@db2+' db2'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind'+

    ' and (db2.uniq<>db1.uniq'+

    ' or db2.pri<>db1.pri'+

    ' or db2.clust<>db1.clust'+

    ' or db2.soid<>db1.soid'+

    ' or db2.csid<>db1.csid))'+

    'union '+

    'select distinct "changed index attributes",server,db,tab,ind,uniq,pri,clust,soid,csid from '+@db2+' db2'+

    ' where exists (select * from '+@db1+' db1'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind'+

    ' and (db2.uniq<>db1.uniq'+

    ' or db2.pri<>db1.pri'+

    ' or db2.clust<>db1.clust'+

    ' or db2.soid<>db1.soid'+

    ' or db2.csid<>db1.csid))'+

    'order by tab,ind,server,db')

    exec('select distinct "changed index attributes (no soid)",server,db,tab,ind,uniq,pri,clust,soid,csid from '+@db1+' db1'+

    ' where exists (select * from '+@db2+' db2'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind'+

    ' and (db2.uniq<>db1.uniq'+

    ' or db2.pri<>db1.pri'+

    ' or db2.clust<>db1.clust))'+

    'union '+

    'select distinct "changed index attributes",server,db,tab,ind,uniq,pri,clust,soid,csid from '+@db2+' db2'+

    ' where exists (select * from '+@db1+' db1'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind'+

    ' and (db2.uniq<>db1.uniq'+

    ' or db2.pri<>db1.pri'+

    ' or db2.clust<>db1.clust))'+

    'order by tab,ind,server,db')

    exec('select "changed index keys",server,db,tab,ind,keyno,keyname from '+@db1+' db1'+

    ' where exists (select * from '+@db2+' db2'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind'+

    ' and db2.keyno=db1.keyno'+

    ' and db2.keyname<>db1.keyname)'+

    'or (select max(keyno) from '+@db1+' d where d.tab=db1.tab and d.ind=db1.ind)<>'+

    ' (select max(keyno) from '+@db2+' d where d.tab=db1.tab and d.ind=db1.ind)'+

    'union '+

    'select "changed index keys",server,db,tab,ind,keyno,keyname from '+@db2+' db2'+

    ' where exists (select * from '+@db1+' db1'+

    ' where db2.tab=db1.tab'+

    ' and db2.ind=db1.ind'+

    ' and db2.keyno=db1.keyno'+

    ' and db2.keyname<>db1.keyname)'+

    'or (select max(keyno) from '+@db1+' d where d.tab=db2.tab and d.ind=db2.ind)<>'+

    ' (select max(keyno) from '+@db2+' d where d.tab=db2.tab and d.ind=db2.ind)'+

    'order by tab,ind,server,db,keyno')

    go

    if exists (select name from sysobjects where name='sp_compare_db' and xtype='P')

    drop procedure sp_compare_db

    GO

    create procedure sp_compare_db(@db1 sysname, @server1 sysname , @db2 sysname ,@server2 sysname)

    as

    declare @SQLStr as nvarchar (4000)

    declare @db_n1 as sysname

    declare @db_NY1 as sysname

    declare @db_n2 as sysname

    declare @db_NY2 as sysname

    set @db_n1 = @Server1 +'.'+@db1

    set @db_NY1=@Server1 +@db1

    set @db_NY2 = @Server2 +'.'+@db2

    set @db_NY2=@Server2 +@db2

    set @SQLStr = 'if exists (select name from tempdb.dbo.sysobjects where name='+char(39) +'schema_'+ char(39) + ' + ' + char(39)+@db_NY1 +char(39) +' and xtype=' +char(39) +'U' +char(39) +')'

    set @SQLStr =@SQLStr + ' drop table schema_' + @db_NY1

    exec sp_executesql @SQLStr

    set @SQLStr = 'if exists (select name from tempdb.dbo.sysobjects where name='+char(39) +'schema_'+ char(39) + ' + ' + char(39)+@db_NY2 +char(39) +' and xtype=' +char(39) +'U' +char(39) +')'

    set @SQLStr =@SQLStr + ' drop table schema_' + @db_NY2

    exec sp_executesql @SQLStr

    set @SQLStr = 'if exists (select name from tempdb.dbo.sysobjects where name='+char(39) +'schind_'+ char(39) + ' + ' + char(39)+@db_NY1 +char(39) +' and xtype=' +char(39) +'U' +char(39) +')'

    set @SQLStr =@SQLStr + ' drop table schind_' + @db_NY1

    exec sp_executesql @SQLStr

    set @SQLStr = 'if exists (select name from tempdb.dbo.sysobjects where name='+char(39) +'schind_'+ char(39) + ' + ' + char(39)+@db_NY2 +char(39) +' and xtype=' +char(39) +'U' +char(39) +')'

    set @SQLStr =@SQLStr + ' drop table schind_' + @db_NY2

    exec sp_executesql @SQLStr

    exec sp_list_schema @db1 ,@server1

    exec sp_list_schema @db2 ,@server2

    --exec compare_schema @db1, @server1 ,@db2 ,@server2

    exec compare_schema @db_NY1 ,@db_NY2

    GO

    --checkpoint

    --exec compare_schema alis_vl_gr,alis_vl_unum_preprod

    exec sp_compare_db DB1,srv1 , DB2,srv2

    Be shure to run it in the anlayzer with show resukt in grid or you want be able to understand the resutls

    This code runs on SQL Server 7

  • Hello Preethi,

    quote:


    Please check the syntax and input data of the command.

    Hope you have full rights.


    there can't be a permission problem since I am sysadmin. Anything wrong with creating the sp in master?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I have tested the sp from another databse too.

    It worked fine.

    Please Check your T-SQL statement. (Is the database name correct? Is the Schema name DB in the first database?)

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • Hi Preethi,

    quote:


    I have tested the sp from another databse too.

    It worked fine.

    Please Check your T-SQL statement. (Is the database name correct? Is the Schema name DB in the first database?)


    no DB was a typo!

    sp_Compare2Tables 'FVS.DBO.sap_sst_ausgabe', 'FVS_TEST.DBO.sap_sst_ausgabe',1,0

    is the only T-SQL statement fired via QA. I'll keep digging. Interestingly, no matter what DB I select in QA, it states that this DB does not exist.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply