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)

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)