Thursday 30 January 2014

SQL Server: Error Msg 7303

Error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Solution:
If you find above ERROR during Import through T-SQL and It is important to be imported, just restart the SQL Server Services

Sunday 26 January 2014

Sql Server: Index Synchronization

During server performance health check, we saw that on few databases some indexes are missing, its common for DBAs when they are dealing with more than 100 DBs on more than 1 production server. Being a part of DBA activities, to improve server performance we needed to review, compare and synchronize the indexes of databases. 

Below are the steps we followed to synchronize indexes of same structure databases.

Step 1. Check Indexes count for all databases
 Execute GetServerDBsIndexCount

Step 2. Remove Indexes from imperfect databases
 Execute RemoveIndexes 'ImperfectDB'

Step 3. Recreate Indexes from base database to imperfect databases
 Execute CreateIndexesFromMasterDB@MasterDB='SQLCat', @ImperfectDB ='ImperfectDB'

Step 4. Re-check Indexes count for all databases
 Execute GetServerDBsIndexCount

Step 5. Find missing NonClustered, Clustered and Unique constraint Indexes
Execute FindAllMissingIndexes @MasterDB='SQLCat', @ImperfectDB ='ImperfectDB'

If database are on different servers you just need to copy indexes details onto the other server and go through the similar steps.

1. Get Indexes Count of SQL Server Databases
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/*  Script By: Amna Asif  
    Purpose: Get Indexes Total Count on Databases  
*/  
GO  
CREATE PROCEDURE GetServerDBsIndexCount  
AS
BEGIN  
    DECLARE @DatbaseName VARCHAR(200)
      
    CREATE TABLE #IndexTable  
        (  
          DatabaseName VARCHAR(200),  
          IndexesCount INT  
        )  
    
    DECLARE DatabaseList CURSOR  
    FOR  
        SELECT  name  
        FROM    sys.databases  
        WHERE   state_desc = 'ONLINE'  
                AND database_id > 4  
  
    OPEN DatabaseList  
    FETCH NEXT FROM DatabaseList INTO @DatbaseName  
    WHILE @@FETCH_STATUS = 0  
        BEGIN       
        EXEC('  
             INSERT INTO #IndexTable(DatabaseName,IndexesCount)  
             SELECT '''+@DatbaseName+''',COUNT(i.name)  
             FROM       [' + @DatbaseName+ '].sys.indexes i     
             INNER JOIN [' + @DatbaseName+ '].sys.tables t ON i.object_id = t.object_id
             INNER JOIN [' + @DatbaseName+ '].sys.schemas sc ON sc.schema_id=t.schema_id  
             WHERE t.type_desc = ''USER_TABLE''
             AND i.type_desc <> ''Heap'' 
             AND i.is_disabled = 0
             AND i.is_Primary_Key=0
            ')  
            FETCH NEXT FROM DatabaseList INTO @DatbaseName  
        END  
    CLOSE DatabaseList  
    DEALLOCATE DatabaseList  
         
    SELECT  *  
    FROM    #IndexTable  
    ORDER BY IndexesCount ASC, DatabaseName   
     
    DROP TABLE #IndexTable

END


2. Remove All Indexes from a SQL Server Database
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/*
Script By: Amna Asif
Purpose: Remove All Indexes from a Database
*/
CREATE PROCEDURE RemoveIndexes  
    (  
      @ImperfectDatabase VARCHAR(200)  
    )  
AS 
BEGIN    
SET NOCOUNT ON      
       
CREATE TABLE #DBIndexesList
(
TableName VARCHAR(1000),
TableID BIGINT,
IndexName VARCHAR(1000),
IndexID BIGINT,
SchemaName VARCHAR(1000),
IndexType VARCHAR(1000)
)       
 ---------------------------------------------------                     
EXEC('
    INSERT INTO #DBIndexesList
    SELECT OBJECT_NAME( i.[object_id], DB_ID('''+@ImperfectDatabase+''') ) TableName,
           i.[object_id] ObjectID,
           i.NAME IndexNAme,
           i.index_id IndexID,
           SC.name SchemaName,
           CASE WHEN i.is_disabled = 1 THEN ''Disabled''

           WHEN i.is_primary_key = 1 AND i.type_desc = ''CLUSTERED'' THEN ''PrimaryClustered''
           WHEN i.is_primary_key = 1 AND i.type_desc = ''NONCLUSTERED'' THEN ''PrimaryNonClustered''

           WHEN (is_unique = 1 AND is_unique_constraint = 0) AND i.type_desc = ''CLUSTERED'' THEN ''UniqueClusteredIndex'' 
           WHEN (is_unique = 1 AND is_unique_constraint = 0) AND i.type_desc = ''NONCLUSTERED'' THEN ''UniqueNonClusteredIndex''

           WHEN (is_unique_constraint = 1 AND is_unique = 1) AND i.type_desc = ''CLUSTERED'' THEN ''UniqueConstraintClustered''             
           WHEN (is_unique_constraint = 1 AND is_unique = 1) AND i.type_desc = ''NONCLUSTERED'' THEN ''UniqueConstraintNonClustered''

           ELSE CASE WHEN i.type_desc = ''CLUSTERED'' THEN ''Clustered''
                     WHEN i.type_desc = ''NONCLUSTERED'' THEN ''NonClustered''
                     ELSE ''''  END 
END
FROM ['+@ImperfectDatabase+'].sys.indexes i
LEFT JOIN ['+@ImperfectDatabase+'].information_schema.table_constraints tc ON i.NAME = tc.constraint_name 
AND OBJECT_NAME(i.object_id, DB_ID('''+@ImperfectDatabase+''')) = tc.table_name
INNER JOIN ['+@ImperfectDatabase+'].SYS.tables T ON i.[object_id] = T.[object_id]
INNER JOIN ['+@ImperfectDatabase+'].SYS.SCHEMAS SC ON SC.[SCHEMA_ID] = T.[SCHEMA_ID]
WHERE t.type_desc = ''USER_TABLE''
AND i.type_desc <> ''Heap'' 
AND i.is_disabled = 0
AND i.is_Primary_Key=0
--AND is_unique = 1 AND is_unique_constraint =0
') 
---------------------------------------------------
      
DECLARE @idxTableName SYSNAME  
      , @idxTableID INT  
      , @idxname SYSNAME  
      , @idxid INT  
      , @indextype VARCHAR(200)
      , @SqlStatement VARCHAR(4000)   
      , @SchemaName VARCHAR(200)  
  
    DECLARE curidx CURSOR    
    FOR  
     SELECT TableName, TableID, IndexName, IndexID, SchemaName, IndexType
     FROM   #DBIndexesList 
     ORDER BY IndexType, TableName, IndexName   

    OPEN curidx  
    FETCH NEXT FROM curidx INTO @idxTableName, @idxTableID, @idxname, @idxid, @SchemaName ,@indextype   
    WHILE ( @@FETCH_STATUS = 0 )  
    BEGIN  
            
         IF  (  @indextype IN ('UniqueConstraintClustered','UniqueConstraintNonClustered'))
         BEGIN 
              SET @SqlStatement = '  IF  EXISTS (SELECT * FROM ' 
                                  + @ImperfectDatabase + '.sys.indexes 
                                    WHERE object_id = OBJECT_ID('''+@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+''')
                                    AND name = '''+@idxname+''' )'
                                  + CHAR(10)
                                  +' ALTER TABLE '+@ImperfectDatabase+'.[dbo].['+@idxTableName+'] DROP CONSTRAINT ['+@idxname+']'
                                  + CHAR(10) 
                                  + CHAR(10) 
    END 
    ELSE
    BEGIN             
        SET @SqlStatement = ' IF EXISTS (SELECT 1 FROM [' + @ImperfectDatabase + '].SYS.INDEXES WHERE name = ''' + @idxname  
                            + ''' AND OBJECT_ID = OBJECT_ID('''+@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+''')) '
                            + CHAR(10) 
                            + 'DROP INDEX [' + @idxname + '] ON [' + @ImperfectDatabase + '].'
                            + @SchemaName + '.['   + @idxTableName + '] ' 
                            + CHAR(10) 
                            + CHAR(10)                  
       END                

      PRINT ( '-----------------Table=['+@idxTableName+'] -- Index=[' + @idxname+']-----------------' )  
      PRINT ( @SqlStatement )                        
         
            FETCH NEXT FROM curidx INTO @idxTableName, @idxTableID, @idxname, @idxid, @SchemaName,@indextype
        END  
    CLOSE curidx  
    DEALLOCATE curidx 
        
DROP TABLE #DBIndexesList    

END

3. Re-Create Indexes on a Database From a Master\Base Database
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/*
Script By: This script is only modified by Aasim Abdullah\ Amna Asif 
Purpose: Re-Create Indexes on Database From a Base Database
*/       
CREATE PROCEDURE CreateIndexesFromMasterDB  
    (  
      @MasterDatabase VARCHAR(200),
      @ImperfectDatabase VARCHAR(200)  
    )  
AS      
BEGIN       
CREATE TABLE #DBIndexesList
(
TableName VARCHAR(1000),
TableID BIGINT,
IndexName VARCHAR(1000),
IndexID BIGINT,
SchemaName VARCHAR(1000),
IndexType VARCHAR(1000)
)       
      
---------------------------------------------------                     
EXEC('INSERT INTO #DBIndexesList
      SELECT  OBJECT_NAME( i.[object_id], DB_ID('''+@MasterDatabase+''') ) TableName,
       i.[object_id] ObjectID,
       i.NAME IndexNAme,
       i.index_id IndexID,
       SC.name SchemaName,

       CASE WHEN i.is_disabled = 1 THEN ''Disabled''

            WHEN i.is_primary_key = 1 AND i.type_desc = ''CLUSTERED'' THEN ''PrimaryClustered''
               WHEN i.is_primary_key = 1 AND i.type_desc = ''NONCLUSTERED'' THEN ''PrimaryNonClustered''

            WHEN (is_unique = 1 AND is_unique_constraint = 0) AND i.type_desc =''CLUSTERED'' THEN ''UniqueClusteredIndex'' 
            WHEN (is_unique = 1 AND is_unique_constraint = 0) AND i.type_desc =''NONCLUSTERED'' THEN ''UniqueNonClusteredIndex''

            WHEN (is_unique_constraint = 1 AND is_unique = 1) AND i.type_desc =''CLUSTERED'' THEN ''UniqueConstraintClustered''             
            WHEN (is_unique_constraint = 1 AND is_unique = 1) AND i.type_desc =''NONCLUSTERED'' THEN ''UniqueConstraintNonClustered''

                   ELSE CASE WHEN i.type_desc = ''CLUSTERED'' THEN ''Clustered''
                      WHEN i.type_desc = ''NONCLUSTERED'' THEN ''NonClustered''
        ELSE ''''  END

       END
       FROM ['+@MasterDatabase+'].sys.indexes i
       LEFT JOIN ['+@MasterDatabase+'].information_schema.table_constraints tc ON i.NAME = tc.constraint_name
       AND OBJECT_NAME(i.object_id, DB_ID('''+@MasterDatabase+''')) = tc.table_name
       INNER JOIN ['+@MasterDatabase+'].SYS.tables T ON i.[object_id] = T.[object_id]
       INNER JOIN ['+@MasterDatabase+'].SYS.SCHEMAS SC ON SC.[SCHEMA_ID] = T.[SCHEMA_ID]
       WHERE t.type_desc = ''USER_TABLE''
       AND i.type_desc <> ''Heap'' 
       AND i.is_disabled = 0
       AND i.is_Primary_Key=0
') 

---------------------------------------------------    
DECLARE @idxTableName SYSNAME  
      , @idxTableID INT  
      , @idxname SYSNAME  
      , @idxid INT  
      , @indextype VARCHAR(200)
      , @colCount INT  
      , @IxColumn SYSNAME  
      , @ColumnIDInTable INT  
      , @ColumnIDInIndex INT  
      , @IsIncludedColumn INT  
      , @sIncludeCols VARCHAR(4000)  
      , @sIndexCols VARCHAR(4000)  
      , @SqlStatement VARCHAR(4000)  
      , @rowcnt INT  
      , @sParamSQL VARCHAR(4000)  
      , @location SYSNAME  
      , @fillfactor INT  
      , @SchemaName VARCHAR(200)  
  
  
-- Get all the index info  
    DECLARE curidx CURSOR    ---- Add sys. with databse name 
    FOR  
     
     SELECT TableName, TableID, IndexName, IndexID, SchemaName, IndexType
     FROM   #DBIndexesList 
     ORDER BY IndexType, TableName, IndexName   

    OPEN curidx  
    FETCH NEXT FROM curidx INTO @idxTableName, @idxTableID, @idxname, @idxid, @SchemaName ,@indextype 
--loop  
    WHILE ( @@FETCH_STATUS = 0 )  
        BEGIN  
            
            IF  (  @indextype IN ('UniqueConstraintClustered','UniqueConstraintNonClustered'))
     BEGIN 
  SET @SqlStatement = '  IF  EXISTS (SELECT * FROM '+ @ImperfectDatabase 
                                     + '.sys.indexes WHERE object_id = OBJECT_ID('''+@ImperfectDatabase+'.'
                                     + @SchemaName+'.'+@idxTableName+''') AND name = '''+@idxname+''' )'
         + CHAR(10)
         +' ALTER TABLE '+@ImperfectDatabase+'.[dbo].['+@idxTableName+'] DROP CONSTRAINT ['+@idxname+']'
         + CHAR(10) 
         + ' IF EXISTS (SELECT 1 FROM [' + @ImperfectDatabase + '].sys.objects WHERE OBJECT_ID=OBJECT_ID(''' 
                                     + @ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+ + ''') )' 
         + CHAR(10)  
         + ' BEGIN IF NOT EXISTS (SELECT 1 FROM [' + @ImperfectDatabase 
                                     + '].SYS.INDEXES WHERE name = ''' + @idxname  
         + ''' AND OBJECT_ID=OBJECT_ID(''' + +@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+ + ''') )'  
                       + CHAR(10) 

    END 
    ELSE
    BEGIN
       SET @SqlStatement = ' IF EXISTS (SELECT 1 FROM [' + @ImperfectDatabase + '].SYS.INDEXES WHERE name = ''' + @idxname  
      + ''' AND OBJECT_ID = OBJECT_ID('''+@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+''')) '
      + CHAR(10) 
      + 'DROP INDEX [' + @idxname + '] ON [' + @ImperfectDatabase + '].' 
                                  + @SchemaName + '.['   + @idxTableName + '] ' 
      + CHAR(10)  
      + ' IF EXISTS (SELECT 1 FROM [' + @ImperfectDatabase 
                                  + '].sys.objects WHERE OBJECT_ID=OBJECT_ID(''' +@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+ ''') )' 
      + CHAR(10)  
      + ' BEGIN IF NOT EXISTS (SELECT 1 FROM [' + @ImperfectDatabase + '].SYS.INDEXES WHERE name = ''' + @idxname  
      + ''' AND OBJECT_ID=OBJECT_ID(''' + +@ImperfectDatabase+'.'+@SchemaName+'.'+@idxTableName+ + ''') )'  
      + CHAR(10) 
                
       END                
                
            SET @SqlStatement = @SqlStatement + 'BEGIN' + CHAR(10)  


SELECT @SqlStatement= @SqlStatement+ CASE @indextype 
                              WHEN 'CLUSTERED' 
                              THEN ' CREATE CLUSTERED '+ 'INDEX [' + @idxname + '] ON [' 
                                          + @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']' 
 
                              WHEN 'UniqueClusteredIndex' 
                              THEN ' CREATE UNIQUE CLUSTERED '+ 'INDEX [' + @idxname + '] ON [' 
                                          + @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']' 
 
                              WHEN 'UniqueConstraintClustered' 
                              THEN ' ALTER TABLE [' + @ImperfectDatabase + '].' + @SchemaName + '.['   + @idxTableName + '] '
                                          +'ADD  CONSTRAINT ['+@idxname+'] UNIQUE CLUSTERED '
  
                              WHEN 'NonClustered' 
                              THEN ' CREATE NONCLUSTERED '+ 'INDEX [' + @idxname + '] ON [' 
                                          + @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']' 
  
                              WHEN 'UniqueNonClusteredIndex' 
                              THEN ' CREATE UNIQUE NONCLUSTERED '+ 'INDEX [' + @idxname + '] ON [' 
                                          + @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']' 
             
                              WHEN 'UniqueConstraintNonClustered' 
                              THEN ' ALTER TABLE [' + @ImperfectDatabase + '].' + @SchemaName + '.['   + @idxTableName + '] '
                                          +'ADD  CONSTRAINT ['+@idxname+'] UNIQUE NONCLUSTERED '
  
                          ELSE ' CREATE NONCLUSTERED '+ 'INDEX [' + @idxname + '] ON [' 
                                      + @ImperfectDatabase + '].' + @SchemaName + '.[' + @idxTableName + ']' 
                          END 

            SET @SqlStatement = @SqlStatement + CHAR(10) + '('  
            
            SET @SqlStatement = @SqlStatement + CHAR(10)
             
            SET @colCount = 0  
            
            SELECT  @fillfactor = fill_factor  
            FROM    sys.indexes  
            WHERE   name = @idxname  
            
            
            IF ISNULL(@fillfactor, 0) = 0 SET @fillfactor = 90  
            
            
-- Get the number of cols in the index  
            SELECT  @colCount = COUNT(*)  
            FROM    sys.index_columns ic  
                    INNER JOIN sys.columns sc ON ic.object_id = sc.object_id  
                                                 AND ic.column_id = sc.column_id  
            WHERE   ic.object_id = @idxtableid  
                    AND index_id = @idxid  
                    AND ic.is_included_column = 0  
                
                    
-- Get the file group info  
            SELECT  @location = f.[name]  
            FROM    sys.indexes i  
                    INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id  
                    INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]  
            WHERE   o.object_id = @idxTableID  
                    AND i.index_id = @idxid  
                           
                    
-- Get all columns of the index  
            DECLARE curidxcolumn CURSOR  
            FOR  
                SELECT  sc.column_id AS columnidintable ,  
                        sc.NAME ,  
                        ic.index_column_id columnidinindex ,  
                        ic.is_included_column AS isincludedcolumn  
                FROM    sys.index_columns ic  
                        INNER JOIN sys.columns sc ON ic.object_id = sc.object_id  
                                                     AND ic.column_id = sc.column_id  
                WHERE   ic.object_id = @idxTableID  
                        AND index_id = @idxid  
                ORDER BY ic.index_column_id  
                
                
            SET @sIncludeCols = ''  
            SET @sIndexCols = ''  
            SET @rowcnt = 0  
            
            
            OPEN curidxColumn  
            FETCH NEXT FROM curidxColumn INTO @ColumnIDInTable, @IxColumn, @ColumnIDInIndex, @IsIncludedColumn  
--loop  
            WHILE ( @@FETCH_STATUS = 0 )  
                BEGIN  
                    IF @IsIncludedColumn = 0  
                        BEGIN  
                            SET @rowcnt = @rowcnt + 1  
                            SET @sIndexCols = CHAR(9) + @sIndexCols + '['+ @IxColumn + ']'  
                            
                            
-- Check the sort order of the index cols  
                            IF ( INDEXKEY_PROPERTY(@idxTableID, @idxid,  @ColumnIDInIndex,  'IsDescending') ) = 0  
                                SET @sIndexCols = @sIndexCols + ' ASC '  
                            ELSE  
                                SET @sIndexCols = @sIndexCols + ' DESC '  
                            IF @rowcnt < @colCount  
                                SET @sIndexCols = @sIndexCols + ', '  
                        END  
                    ELSE  
                        BEGIN  
-- Check for any include columns  
                            IF LEN(@sIncludeCols) > 0  
                                SET @sIncludeCols = @sIncludeCols + ','  
                            SET @sIncludeCols = @sIncludeCols + '['  
                                + @IxColumn + ']'  
                        END  
                    FETCH NEXT FROM curidxColumn INTO @ColumnIDInTable,  
                        @IxColumn, @ColumnIDInIndex, @IsIncludedColumn  
                END  
            CLOSE curidxColumn  
            DEALLOCATE curidxColumn  
  
  
--append to the result  
            IF LEN(@sIncludeCols) > 0  
                SET @sIndexCols = @SqlStatement + @sIndexCols + CHAR(10)+ ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '   + CHAR(10)+ CHAR(10)+ CHAR(10)
            ELSE  
                SET @sIndexCols = @SqlStatement + @sIndexCols + CHAR(10) + ') '   + CHAR(10)+ CHAR(10)+ CHAR(10)
                
-- Build the options  
            SET @sParamSQL = ' WITH (FILLFACTOR = ' + CAST(ISNULL(@fillfactor, 90) AS VARCHAR(3)) + ', '  
                        
            IF ( INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1 )  
                SET @sParamSQL = @sParamSQL + ' PAD_INDEX = ON, '  
                
            ELSE  
                SET @sParamSQL = @sParamSQL + ' PAD_INDEX = OFF, '  
                
            IF ( INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1 )  
                SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '  
                
            ELSE  
                SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '  
                
            IF ( INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1 )  
                SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ON, '  
                
            ELSE  
                SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = on, '  
                
            IF ( INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1 )  
                SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ON, '  
                
            ELSE  
                SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = OFF ' 
                
                 
            SET @sParamSQL = CASE @indextype WHEN  'UniqueConstraintClustered' THEN @sParamSQL+' )'
                                             WHEN 'UniqueConstraintNonClustered' THEN @sParamSQL+' )'
                                             ELSE @sParamSQL + ', DROP_EXISTING = off ) '  END
            SET @SqlStatement = @sIndexCols + CHAR(10) + @sParamSQL + ' ON [' + @location + ']' + CHAR(10) + 'END  END' + CHAR(10)  
            

            PRINT ( '-----------------Table=['+@idxTableName+'] -- Index=[' + @idxname+']-----------------' )  
            PRINT ( @SqlStatement )  
        
         
            FETCH NEXT FROM curidx INTO @idxTableName, @idxTableID, @idxname, @idxid, @SchemaName,@indextype
        END  
    CLOSE curidx  
    DEALLOCATE curidx     
    
DROP TABLE #DBIndexesList 

END   

4. Find missing Indexes by comparing indexes of Mater and Slave Database.
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
/*
Script By: Amna Asif
Purpose: Find missing Indexes
*/
GO
CREATE PROCEDURE FindAllMissingIndexes(@MasterDB varchar(200),@ImperfectDB varchar(200) )
AS

EXEC('
SELECT  t.name ObjectName, i.name IndexName
FROM    '+@MasterDB+'.sys.indexes i
        INNER JOIN '+@MasterDB +'.sys.tables t ON i.object_id = t.object_id
        INNER JOIN '+@MasterDB +'.sys.schemas sc ON sc.schema_id = t.schema_id
WHERE   t.type_desc = ''USER_TABLE''
        AND i.is_primary_key = 0
        AND i.is_disabled = 0

EXCEPT

SELECT  t.name ObjectName, i.name IndexName
FROM    '+@ImperfectDB+'.sys.indexes i
        INNER JOIN '+@ImperfectDB+'.sys.tables t ON i.object_id = t.object_id
        INNER JOIN '+@ImperfectDB+'.sys.schemas sc ON sc.schema_id = t.schema_id
WHERE   t.type_desc = ''USER_TABLE''
        AND i.is_primary_key = 0
        AND i.is_disabled = 0
        ')