June 11, 2003 at 6:19 am
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]
June 11, 2003 at 8:28 am
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]
June 11, 2003 at 8:45 am
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
June 11, 2003 at 9:10 am
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]
June 11, 2003 at 9:44 am
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>
June 11, 2003 at 10:43 pm
Please check the syntax and input data of the command.
Hope you have full rights.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
June 11, 2003 at 10:44 pm
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/
June 11, 2003 at 11:33 pm
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
June 12, 2003 at 12:16 am
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]
June 12, 2003 at 1:59 am
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/
June 12, 2003 at 2:14 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply