Compare indexes between two databases

Question

Compare indexes between two databases

DBA-640728

SSChampion

Points: 12898
More actions
May 13, 2009 at 2:56 pm

#130860

hello I am trying the compare indexes between two databases, and then create a script to be able add the missing indexes in my second db, is there an easier way to do this than manually, if i execute the exec sp_helpindex 'tablename" i don't get the included columns, does anyone have a script that could help me with this??

Viewing 8 posts - 1 through 7 (of 7 total)

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

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

Have you tried using SQL Compare from RedGate or even the Schema Compare tool included with Visual Studio 2008 Database Edition?

A.J.
DBA with an attitude

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

i have sql compare but is the version that you can not compare indexes, and i don't have visual studio 2008, do you have another way?

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I found this in my old SQL 2000 scripts and it will work in 2005. You could put the 2 sets of results to work tables and then compare them.

select g.name as 'Table', d.name as 'Index_Name', f.name as 'FieldName'

from sysindexes d

join sysindexkeys e on d.id = e.id and d.indid = e.indid

join syscolumns f on f.id = d.id and f.colid = e.colid

join sysobjects g on g.id = d.id

where g.xtype = 'U'

order by g.name,d.name, e.keyno

I'm sure there's a better way using newer 2005 functions, but that's what I have on hand.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

You could try doing it by hand using a query similar to the following:

select o.name as TableName,

i.name as IndexName,

(

SELECT c.name + ', '

FROM sys.index_columns ic

JOIN sys.columns c ON ic.column_id = c.column_id and ic.object_id = c.object_id

WHERE i.object_id = ic.object_id and i.index_id = ic.index_id

AND ic.is_included_column = 0

ORDER BY ic.index_column_id

FOR XML PATH('')

) AS Key_Columns,

(

SELECT c.name + ', '

FROM sys.index_columns ic

JOIN sys.columns c ON ic.column_id = c.column_id and ic.object_id = c.object_id

WHERE i.object_id = ic.object_id and i.index_id = ic.index_id

AND ic.is_included_column = 1

ORDER BY ic.index_column_id

FOR XML PATH('')

) AS IncludedColumns,

i.type_desc as IndexType,

i.is_unique as IsUnique,

i.is_primary_key as IsPrimaryKey

from sys.indexes i

join sys.objects o on i.object_id = o.object_id

where o.is_ms_shipped = 0

If the databases are on the same server, you could put the database names into the query, and use the EXCEPT operation to identify what is different between the two databases. Just make sure that you do it both directions, so

Query from Database1

EXCEPT

Query from Database2

and then

Query from Database2

EXCEPT

Query from Database1

That would be a brute force way of going about it.

DBA-640728 SSChampion Points: 12898 More actions · Answer 5

thank you what about if the databases are in different servers

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

over linked servers it should still work you just have to use ... for the DMV's so with a link named Development, and the AdventureWorks Database it would be Development.AdventureWorks.sys.objects. Just 4 part name the Linked side of the EXCEPT query and it should still work correctly.

DBA-640728 SSChampion Points: 12898 More actions · Answer 7

thank you so much, your query works perfectly :-). I finally got visual studio 2008, do you know where i can get the step of how to do the schema compare?