Compare Indexes/Constraints between two SQL Server databases

Question

I have two databases on one SQL Server that I would like to compare Index/Constraints. The two databases should be identical but I have a suspicion that one database is missing a index or constraints. Is there a way to script this out to compare the two?

Thank you!

Answer 1

DECLARE @Sourcedb sysname 
DECLARE @Destdb sysname 
DECLARE @SQL varchar(max) 
SELECT @Sourcedb = '<<Database1>>' 
SELECT @Destdb = '<<Database2>>' 
SELECT @SQL = ' SELECT ISNULL(SoSource.name,SoDestination.name) ''Object Name'' 
 , CASE 
 WHEN SoSource.object_id IS NULL THEN SoDestination.type_desc + '' missing in the source -- ' 
 + @Sourcedb + ''' COLLATE database_default 
 WHEN SoDestination.object_id IS NULL THEN SoSource.type_desc + '' missing in the Destination -- ' + @Destdb 
 + ''' COLLATE database_default 
 ELSE SoDestination.type_desc + '' available in both Source and Destination'' COLLATE database_default 
 END ''Status'' 
 FROM (SELECT * FROM ' + @Sourcedb + '.SYS.objects 
 WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoSource 
 FULL OUTER JOIN (SELECT * FROM ' + @Destdb + '.SYS.objects 
 WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoDestination 
 ON SoSource.name = SoDestination.name COLLATE database_default 
 AND SoSource.type = SoDestination.type COLLATE database_default 
 ORDER BY isnull(SoSource.type,SoDestination.type)' 
EXEC (@Sql)

• AAA: Problem solved! See solution above from: gallery.technet.microsoft.com/scriptcenter/…

Answer 2

That query doesn't work. You need to use sys.indexes and sys.objects.

Moreover, since Primary Keys are Indexes indeed in Sql Server, if you follow a naming convention where creating indexes, you can use the prefix for filtering out Primary Keys from 'other' indexes.

In the following example, i use IX as prefix for indexes, but any other will work:

DECLARE @Sourcedb SYSNAME 
DECLARE @Destdb SYSNAME 
DECLARE @IndexPrefix VARCHAR(max) 
DECLARE @SQL VARCHAR(max) 
SELECT @Sourcedb = '@Sourcedb' 
SELECT @Destdb = '@Destdb' 
SELECT @IndexPrefix = 'IX' 
SELECT @SQL = ' SELECT ix.name,ob.type,ob.type_desc FROM ' 
 + @Sourcedb + '.SYS.indexes ix inner join ' 
 + @Sourcedb 
 + 
'.sys.objects ob on ob.object_id = ix.object_id where ob.type <> ''S'' and ix.name like '' ' 
 + @IndexPrefix 
 + '%'' and ix.name not in ( select db2.name from (SELECT ix.name,ob.type,ob.type_desc FROM ' 
 + @Destdb + '.SYS.indexes ix inner join ' 
 + @Destdb 
 + 
'.sys.objects ob on ob.object_id = ix.object_id where ob.type <> ''S'' and ix.name like '' ' 
 + @IndexPrefix + '%'') db2) ' 
EXEC (@Sql)