IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[CP_DB_Check]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[CP_DB_Check] 
END
GO

CREATE PROCEDURE [dbo].[CP_DB_Check] @Db1 SYSNAME, @Db2 SYSNAME, @Tipo CHAR(2)=NULL

AS
DECLARE @Sql NVARChAR(MAX)

IF @Tipo IS NULL SET @Tipo = '*'

SET @Tipo = UPPER(@Tipo)

IF @Tipo IN ('*','T')
BEGIN
	SELECT 'Tablas Faltantes *********************************************************'
	SET @Sql = ' SELECT * FROM '+ @Db1 +'.[INFORMATION_SCHEMA].TABLES a left join '+ @Db2 +'.[INFORMATION_SCHEMA].TABLES b
					 on   a.TABLE_NAME COLLATE Modern_Spanish_CI_AS = b.TABLE_NAME COLLATE Modern_Spanish_CI_AS 
					 WHERE b.TABLE_NAME IS NULL
					 AND a.TABLE_NAME NOT LIKE ''z%''
					 AND a.TABLE_NAME NOT IN (''userinfo'',''userinfo_attarea'',''GV02ART'',''acc_levelset_emp'')
					 ORDER BY a.TABLE_NAME'

	PRINT @Sql
	EXECUTE sp_executesql  @Sql
END
		
IF @Tipo IN ('*','CF')
BEGIN
	SELECT 'Campos Faltantes *********************************************************'
	SET @Sql = ' SELECT * FROM '+ @Db1 +'.[INFORMATION_SCHEMA].COLUMNS a left join '+ @Db2 +'.[INFORMATION_SCHEMA].COLUMNS b
					 on   a.TABLE_NAME COLLATE Modern_Spanish_CI_AS = b.TABLE_NAME COLLATE Modern_Spanish_CI_AS 
					 and  a.COLUMN_NAME COLLATE Modern_Spanish_CI_AS = b.COLUMN_NAME COLLATE Modern_Spanish_CI_AS
					 WHERE b.COLUMN_NAME IS NULL
					 AND a.TABLE_NAME NOT LIKE ''z%''
					 AND a.TABLE_NAME NOT IN (''userinfo'',''userinfo_attarea'',''GV02ART'',''acc_levelset_emp'')
					 ORDER BY a.TABLE_NAME, a.COLUMN_NAME '

	PRINT @Sql
	EXECUTE sp_executesql  @Sql
END
		
IF @Tipo IN ('*','CD')
BEGIN
	SELECT 'Campos Diferentes *********************************************************'
	SET @Sql = ' SELECT * FROM '+ @Db1 +'.[INFORMATION_SCHEMA].COLUMNS a, '+ @Db2 +'.[INFORMATION_SCHEMA].COLUMNS b
					 WHERE  a.TABLE_NAME COLLATE Modern_Spanish_CI_AS = b.TABLE_NAME COLLATE Modern_Spanish_CI_AS 
					 and  a.COLUMN_NAME COLLATE Modern_Spanish_CI_AS = b.COLUMN_NAME COLLATE Modern_Spanish_CI_AS
					 AND a.TABLE_NAME NOT LIKE ''z%''
					 AND a.TABLE_NAME NOT IN (''userinfo'',''userinfo_attarea'',''GV02ART'',''acc_levelset_emp'')
					 AND (a.DATA_TYPE COLLATE Modern_Spanish_CI_AS <> b.DATA_TYPE COLLATE Modern_Spanish_CI_AS
						OR a.IS_NULLABLE COLLATE Modern_Spanish_CI_AS <> b.IS_NULLABLE COLLATE Modern_Spanish_CI_AS
						OR a.CHARACTER_MAXIMUM_LENGTH <> b.CHARACTER_MAXIMUM_LENGTH
						OR a.NUMERIC_PRECISION <> b.NUMERIC_PRECISION
						OR a.NUMERIC_SCALE <> b.NUMERIC_SCALE
						) 
					 ORDER BY a.TABLE_NAME, a.COLUMN_NAME '

	PRINT @Sql
	EXECUTE sp_executesql  @Sql
END

IF @Tipo IN ('*','KD')
BEGIN
	SELECT 'CONSTRAINTS Faltantes *********************************************************'
	SET @Sql = ' SELECT * FROM '+ @Db1 +'.[INFORMATION_SCHEMA].TABLE_CONSTRAINTS a left join '+ @Db2 +'.[INFORMATION_SCHEMA].TABLE_CONSTRAINTS b
					 on   a.TABLE_NAME COLLATE Modern_Spanish_CI_AS = b.TABLE_NAME COLLATE Modern_Spanish_CI_AS 
					 WHERE b.TABLE_NAME IS NULL
					 AND a.TABLE_NAME NOT LIKE ''z%''
					 AND a.TABLE_NAME NOT IN (''userinfo'',''userinfo_attarea'',''GV02ART'',''acc_levelset_emp'')
					 ORDER BY a.TABLE_NAME'

	PRINT @Sql
	EXECUTE sp_executesql  @Sql
END
		

IF @Tipo IN ('*','KF')
BEGIN
	SELECT 'CONSTRAINTS Diferentes *********************************************************'
	SET @Sql = ' SELECT * FROM '+ @Db1 +'.[INFORMATION_SCHEMA].KEY_COLUMN_USAGE a, '+ @Db2 +'.[INFORMATION_SCHEMA].KEY_COLUMN_USAGE b
					 WHERE  a.TABLE_NAME COLLATE Modern_Spanish_CI_AS = b.TABLE_NAME COLLATE Modern_Spanish_CI_AS 
					 AND  a.COLUMN_NAME COLLATE Modern_Spanish_CI_AS = b.COLUMN_NAME COLLATE Modern_Spanish_CI_AS
					 AND  a.CONSTRAINT_NAME COLLATE Modern_Spanish_CI_AS = b.CONSTRAINT_NAME COLLATE Modern_Spanish_CI_AS
					 AND a.TABLE_NAME NOT LIKE ''z%''
					 AND a.TABLE_NAME NOT IN (''userinfo'',''userinfo_attarea'',''GV02ART'',''acc_levelset_emp'')
					 AND (a.ORDINAL_POSITION  <> b.ORDINAL_POSITION ) 
					 ORDER BY a.TABLE_NAME, a.COLUMN_NAME '

	PRINT @Sql
	EXECUTE sp_executesql  @Sql
END

/*

EXEC CP_DB_Check 'Clever_CDA','Clever_Ferro'
o
EXEC CP_DB_Check 'Clever_CDA','Clever_Ferro','*'   --toda la info

EXEC CP_DB_Check 'Clever_CDA','Clever_Ferro','T'   --tablas faltantes

EXEC CP_DB_Check 'Clever_CDA','Clever_Ferro','CF'   --campos faltantes

EXEC CP_DB_Check 'Clever_CDA','Clever_Ferro','CD'   --campos diferentes

EXEC CP_DB_Check 'Clever_CDA','Clever_Ferro','KD'   --CONSTRAINTS faltantes

EXEC CP_DB_Check 'Clever_CDA','Clever_Ferro','KF'   --CONSTRAINTS diferentes


*/


