1

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!

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)
0

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) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.