Compare Different tables from 2 Databases

exec dbo.PD_Lista_Tabela_Diferente 'databaseNew' , 'databaseOld', '' -- sem restriçao de nome da tabela

exec dbo.PD_Lista_Tabela_Diferente 'databaseNew' , 'databaseOld'', 'p' -- tab. que começao com "p"

/* 

Autor - Jose Anchieta Carvalho Junior ~ jrtupa@gmail.com

*/    


IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'PD_Lista_Tabela_Diferente' AND TYPE = 'P')

 DROP PROCEDURE DBO.PD_Lista_Tabela_Diferente

GO



CREATE PROCEDURE DBO.PD_Lista_Tabela_Diferente

            @DBFontes varchar(50),

            @DBVerifica varchar(50), 

            @Iniciais_Nome_Tabela varchar(50) 

        

WITH ENCRYPTION

AS

 SET nocount off

 

 -- Criando a Tabela Temporária

 IF EXISTS ( SELECT * FROM TEMPDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#TMP_TABELAS_DIFERENTES' ) 

 DROP TABLE #TMP_TABELAS_DIFERENTES

 ELSE

 BEGIN

 -- Criacao da Tabela Temporaria

 CREATE TABLE #TMP_TABELAS_DIFERENTES(

    TABLE_CATALOG_DBFontes varchar(100),

    TABLE_NAME_DBFontes varchar(100),

    COLUMN_NAME_DBFontes varchar(100),

    DATA_TYPE_DBFontes     varchar(100),    



    TABLE_CATALOG_DBVerifica varchar(100),

    TABLE_NAME_DBVerifica varchar(100),

    COLUMN_NAME_DBVerifica varchar(100),

    DATA_TYPE_DBVerifica varchar(100)

 ) 

 END 



 DECLARE 

 @STR varchar(1000);



 SET @STR = 'SELECT ' + 

        ' A.TABLE_CATALOG as TABLE_CATALOG_DBFontes, ' +

        ' A.TABLE_NAME as TABLE_NAME_DBFontes, ' +

        ' A.COLUMN_NAME as COLUMN_NAME_DBFontes, ' +

        ' A.DATA_TYPE as DATA_TYPE_DBFontes, ' +

        ' B.TABLE_CATALOG as TABLE_CATALOG_DBVerifica, ' + 

        ' B.TABLE_NAME as TABLE_NAME_DBVerifica, ' + 

        ' B.COLUMN_NAME as COLUMN_NAME_DBVerifica, ' +

        ' B.DATA_TYPE as DATA_TYPE_DBVerifica ' +



        'FROM ' + @DBFontes      + '.INFORMATION_SCHEMA.COLUMNS A ' +

        'INNER JOIN ' + @DBFontes + '.INFORMATION_SCHEMA.TABLES T ON A.TABLE_CATALOG = T.TABLE_CATALOG AND ' +

                     + ' A.TABLE_SCHEMA = T.TABLE_SCHEMA AND ' + 

                     + ' A.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = ''BASE TABLE'' ' +

        'LEFT JOIN ' + @DBVerifica + '.INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME ' +

     'WHERE ( B.COLUMN_NAME IS NULL OR ' + 

        ' A.COLUMN_NAME IS NULL OR ' + 

        ' A.DATA_TYPE <> B.DATA_TYPE ) ' + 

        ' AND A.TABLE_NAME LIKE '''+@Iniciais_Nome_Tabela+'%'' '+ 

        'ORDER BY A.TABLE_NAME, A.COLUMN_NAME ';



 INSERT INTO #TMP_TABELAS_DIFERENTES (TABLE_CATALOG_DBFontes, TABLE_NAME_DBFontes, COLUMN_NAME_DBFontes, DATA_TYPE_DBFontes,

                 TABLE_CATALOG_DBVerifica, TABLE_NAME_DBVerifica, COLUMN_NAME_DBVerifica, DATA_TYPE_DBVerifica )

 EXECUTE(@STR)



 SET nocount on



 SELECT *

 FROM #TMP_TABELAS_DIFERENTES

 ORDER BY TABLE_NAME_DBFontes, COLUMN_NAME_DBFontes;

GO