system stored procedure

[Karin3]

Is there a system stored procedure that will allow for the comparison of sysobjects between two separate databases? Or does any one have some code handy.
This is for SQL 2000.
Thanx in advance.

 

[tlbroadbent]

Check db_compare script at SWYNK.com. You can find a download at

http://www.swynk.com/sqlscripts/maintenance7.asp.

Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Karin3]

I've checked it out and it is a bit complicated for me. What I need to do is compare indexes between databases. I've only been at SQL for a year and learning as I go.

 

[tlbroadbent]

I've been working on my own version of a procedure to compare indexes. It certainly isn't a finished product but provides some comparisons.

sp_CompareIndexes utilizes an undocumented stored procedure, sp_MShelpindex to get index info. sp_CompareIndexes accepts five parameters:[ol][li]@localdb - name of the first (or local) database
[li]@localtbl - name of the table to analyze
[li]@remotedb - name of the second (or remote) database
[li]@remotetbl - name of the remote table
[li]@remotesrv - name of the remote server of the database you want to compare is on another server.[/ol]The output of the SP consists of three result sets. These are 'On local table/Not on remote table', 'On remote table/Not on local table', and 'Index characteristics differ'.

-------------------------------------
[tt]
Create the following stored procedure in the master database.

CREATE Procedure sp_CompareIndexes
@localdb varchar(40),
@localtbl varchar(40),
@remotedb varchar(40),
@remotetbl varchar(40),
/* @remotesrv names the remote server if the dtabase and table to be compared resides on a server other than the local server.
If you leave out this parameter the procedure looks for the remote database and table on the local server. */
@remotesrv varchar(40)=''
As

Set nocount on

/* SP uses @sql to build dynamic SQL statements. */
Declare @sql nvarchar(2000)

/* The temporary tables, #indx1 and #indx2, will hold the output of the undocumented stored procedure sp_MShelpindex */

Create Table #indx1 (indname varchar(40), status int, indid int, OrigFillFactor int,
IndCol1 varchar(40), IndCol2 varchar(40), IndCol3 varchar(40), IndCol4 varchar(40),
IndCol5 varchar(40), IndCol6 varchar(40), IndCol7 varchar(40), IndCol8 varchar(40),
IndCol9 varchar(40), IndCol10 varchar(40), IndCol11 varchar(40), IndCol12 varchar(40),
IndCol13 varchar(40), IndCol14 varchar(40), IndCol15 varchar(40), IndCol16 varchar(40),
SegName varchar(40), FTKey int)

Create Table #indx2 (indname varchar(40), status int, indid int, OrigFillFactor int,
IndCol1 varchar(40), IndCol2 varchar(40), IndCol3 varchar(40), IndCol4 varchar(40),
IndCol5 varchar(40), IndCol6 varchar(40), IndCol7 varchar(40), IndCol8 varchar(40),
IndCol9 varchar(40), IndCol10 varchar(40), IndCol11 varchar(40), IndCol12 varchar(40),
IndCol13 varchar(40), IndCol14 varchar(40), IndCol15 varchar(40), IndCol16 varchar(40),
SegName varchar(40), FTKey int)

/* Insert output of sp_MShelpindex into #indx1 - for 1st or local table */
Select @sql=
'Insert #indx1 Exec '+
@localdb+'.dbo.sp_mshelpindex ['+
@localtbl+']'

Exec (@sql)

/* Insert output of sp_MShelpindex into #indx2 - for 2nd or remote table */
If @remotesrv=''
Begin
Select @sql=
'Insert #indx2 Exec '+
@remotedb+'.dbo.sp_mshelpindex '''+
@remotetbl+''''
End
Else
Begin
Select @sql=
'Insert #indx2 Select * From Openquery(' +
@remotesrv + ', ''Exec '+
@remotedb+'.dbo.sp_mshelpindex ' + '[' +
@remotetbl + ']' + ''')'
End

Exec (@sql)

/* Select indexes on 1st table that are not on 2nd table. */
Select *,
Cast('On '+@localdb+'.'+@localtbl+
'/Not on '+@remotesrv+'.'+
@remotedb+'.'+@localtbl As varchar(80)) As msg
From #indx1
Where indname not like '_WA%'
And indname Not In (Select indname From #indx2)

/* Select indexes on 2nd table that are not on 1st table. */
Select *,
cast('On '+@remotesrv+'.'+@remotedb+'.'+@localtbl+
'/Not on '+@localdb+'.'+@localtbl As varchar(80)) As msg
From #indx2
Where indname not like '_WA%'
And indname Not In (Select indname From #indx1)

/* Select indexes that occur on both tables but have different characteristics.
Currently only comparing 1st six columns of the index. Can be increased to 16 columns
which is the output of sp_MShelpindex. */
Select
l.indname,
l.status As Lstat,
r.status As Rstat,
l.indcol1 As Lcl_indcol1,
r.indcol1 As Rmt_indcol1,
l.indcol2 As Lcl_indcol2,
r.indcol2 As Rmt_indcol2,
l.indcol3 As Lcl_indcol3,
r.indcol3 As Rmt_indcol3,
l.indcol4 As Lcl_indcol4,
r.indcol4 As Rmt_indcol4,
l.indcol5 As Lcl_indcol5,
r.indcol5 As Rmt_indcol5,
l.indcol6 As Lcl_indcol6,
r.indcol6 As Rmt_indcol6,
cast('Index characteristics differ' As varchar(80)) As msg
From #indx1 l Inner Join #indx2 r On l.indname=r.indname
Where l.indname not like '_WA%' And r.indname not like '_WA%'
And (isnull(l.IndCol1,'')<>isnull(r.IndCol1,'')
Or isnull(l.IndCol2,'')<>isnull(r.IndCol2,'')
Or isnull(l.IndCol3,'')<>isnull(r.IndCol3,'')
Or isnull(l.IndCol4,'')<>isnull(r.IndCol4,'')
Or isnull(l.IndCol5,'')<>isnull(r.IndCol5,'')
Or isnull(l.IndCol6,'')<>isnull(r.IndCol6,'')
Or l.status<>r.status)

/* Drop temp tables */
Drop Table #indx1
Drop Table #indx2

Set nocount off
GO
[/tt]
-------------------------------------

Call the stored procedure like this when both database tables reside on the same server.
[tt]
Exec sp_CompareIndexes
@localdb='HR',
@localtbl='dbo.employees',
@remotedb='enterprise',
@remotetbl='dbo.employees',
@remotesrv=''
[/tt]
Call the stored procedure like this when one of the database tables resides on a remote server.
[tt]
Exec sp_CompareIndexes
@localdb='HR',
@localtbl='dbo.employees',
@remotedb='enterprise',
@remotetbl='dbo.employees',
@remotesrv='server2'
[/tt]
Let me know if you encounter any problems or have any questions. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.