--*******************************************************
--*******************************************************
--*******************************************************

USE [DB]
GO
/****** Object:  StoredProcedure [dbo].[spCOMPARETABLECOLUMNS]    Script Date: 06/01/2009 13:00:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 
CREATE PROCEDURE [dbo].[spCOMPARETABLECOLUMNS]  
	-- Add the parameters for the stored procedure here
	@INSTANCE1 NVARCHAR(35)	, 
	@DATABASE1 NVARCHAR(35), 
	@TABLE1 NVARCHAR(100), 
	@INSTANCE2 NVARCHAR(35), 
	@DATABASE2 NVARCHAR(35), 
	@TABLE2 NVARCHAR(100)

AS

BEGIN

	DECLARE @COMMAND NVARCHAR(4000), 
		@COLUMNS_TABLE1 NVARCHAR(200), 
		@COLUMNS_TABLE2 NVARCHAR(200), 
		@NAME NVARCHAR(50), 
		@IS_NULLABLE VARCHAR(3), 
		@DATA_TYPE NVARCHAR(128), 
		@CHARACTER_MAXIMUM_LENGTH INT, 
		@NUMERIC_PRECISION INT , 
		@NUMERIC_PRECISION_RADIX INT, 
		@NUMERIC_SCALE TINYINT, 
		@DATETIME_PRECISION SMALLINT, 
		@TABLE NVARCHAR(50), 
		@COUNT INT  

	SET @COUNT = 1
	SET @INSTANCE1 = 
		CASE 
			WHEN  (@INSTANCE1 IS NOT NULL) THEN '[' + RTRIM(@INSTANCE1) + '].'
			WHEN (@INSTANCE1 IS NULL) THEN  ''
		END

	SET @DATABASE1 = 
		CASE 
			WHEN  (@DATABASE1 IS NOT NULL) THEN '[' + RTRIM(@DATABASE1) + '].'
			WHEN (@DATABASE1 IS NULL) THEN ''
		END
	
	SET @INSTANCE2 = 
		CASE 
			WHEN  (@INSTANCE2 IS NOT NULL) THEN '[' + RTRIM(@INSTANCE2) + '].'
			WHEN  (@INSTANCE2 IS NULL) THEN ''
		END
	SET @DATABASE2 = 
		CASE 
			WHEN  (@DATABASE2 IS NOT NULL) THEN '[' + RTRIM(@DATABASE2) + '].'
			WHEN  (@DATABASE2 IS NULL) THEN ''
		END
	
	SET @COLUMNS_TABLE1 = RTRIM(@INSTANCE1) + RTRIM(@DATABASE1) + '[INFORMATION_SCHEMA].[COLUMNS] '
	SET @COLUMNS_TABLE2 = RTRIM(@INSTANCE2) + RTRIM(@DATABASE2) + '[INFORMATION_SCHEMA].[COLUMNS] '

	PRINT 'WHAT IS DIFFERENT BETWEEN ' + @TABLE1 + ' AND ' + @TABLE2
	PRINT ''
	PRINT ''

	SELECT @COMMAND = 
		'DECLARE c CURSOR FOR 
			SELECT 
				COLUMN_NAME, 
				DATA_TYPE, 
				CHARACTER_MAXIMUM_LENGTH, 
				NUMERIC_PRECISION,  
				NUMERIC_PRECISION_RADIX, 
				NUMERIC_SCALE, 
				DATETIME_PRECISION, 
				IS_NULLABLE 
			FROM '
				+ @COLUMNS_TABLE1 + ' 
			WHERE 
				TABLE_NAME = ' + CHAR(39) + @TABLE1 + CHAR(39) + ' 
			EXCEPT 
			SELECT 
				COLUMN_NAME, 
				DATA_TYPE, 
				CHARACTER_MAXIMUM_LENGTH, 
				NUMERIC_PRECISION,  
				NUMERIC_PRECISION_RADIX, 
				NUMERIC_SCALE, 
				DATETIME_PRECISION, 
				IS_NULLABLE 
			FROM '
				+ @COLUMNS_TABLE2 + '
			WHERE 
				TABLE_NAME = ' + CHAR(39) + @TABLE2 + CHAR(39)  

	BEGIN TRY 	
	
		EXEC(@COMMAND);

	END TRY 

	BEGIN CATCH 

		PRINT 'ERROR_MESSAGE = ' + ERROR_MESSAGE() + ' ERROR LINE = ' + STR(ERROR_LINE())
		PRINT ''

	END CATCH 

	OPEN c 
	FETCH NEXT FROM 
		c 
	INTO 
		@NAME, 
		@DATA_TYPE, 
		@CHARACTER_MAXIMUM_LENGTH, 
		@NUMERIC_PRECISION, 
		@NUMERIC_PRECISION_RADIX, 
		@NUMERIC_SCALE, 
		@DATETIME_PRECISION, 
		@IS_NULLABLE

	WHILE @@FETCH_STATUS = 0

		BEGIN 

			BEGIN TRY 

				PRINT 'FETCH ' + STR(@COUNT)
				SET @COUNT = @COUNT + 1
				PRINT  'COLUMN:  ' + 
					@NAME + ', ' + 
					@DATA_TYPE + ', ' + 
					CASE 
						WHEN @CHARACTER_MAXIMUM_LENGTH  IS NULL THEN '0' 
						ELSE 	CAST(@CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) 
					END 
					+ ', ' + 
					CASE 
						WHEN @NUMERIC_PRECISION IS NULL THEN '0' 
						ELSE CAST(@NUMERIC_PRECISION AS NVARCHAR(10)) 
					END 
					 + ', ' + 
					CASE 
						WHEN @NUMERIC_PRECISION_RADIX IS NULL THEN '0' 
						ELSE CAST(@NUMERIC_PRECISION_RADIX AS NVARCHAR(10)) 
					END 
					+ ', ' + 
					CASE 
						WHEN @NUMERIC_SCALE IS NULL THEN '0' 
						ELSE CAST(@NUMERIC_SCALE AS NVARCHAR(10)) 
					END 
					+ ', ' + 
					CASE 
						WHEN @DATETIME_PRECISION IS NULL THEN '0' 
						ELSE CAST(@DATETIME_PRECISION AS NVARCHAR(10)) 
					END 
					+ ', ' + 
					@IS_NULLABLE 
				PRINT ''

			END TRY 

			BEGIN CATCH

				PRINT 'ERROR:  ' + ERROR_MESSAGE()
				PRINT ''
 
			END CATCH 

			FETCH NEXT FROM 
				c 
			INTO 
				@NAME, 
				@DATA_TYPE, 
				@CHARACTER_MAXIMUM_LENGTH, 
				@NUMERIC_PRECISION, 
				@NUMERIC_PRECISION_RADIX, 
				@NUMERIC_SCALE, 
				@DATETIME_PRECISION, 
				@IS_NULLABLE

		END

	CLOSE c
	DEALLOCATE c

	PRINT 'WHAT IS DIFFERENT BETWEEN ' + @TABLE2 + ' AND ' + @TABLE1
	PRINT ''
	PRINT ''

	SELECT @COMMAND = 
		'DECLARE c CURSOR FOR 
			SELECT 
				COLUMN_NAME, 
				DATA_TYPE, 
				CHARACTER_MAXIMUM_LENGTH, 
				NUMERIC_PRECISION,  
				NUMERIC_PRECISION_RADIX, 
				NUMERIC_SCALE, 
				DATETIME_PRECISION, 
				IS_NULLABLE 
			FROM ' 
				+ @COLUMNS_TABLE2 + ' 
			WHERE 
				TABLE_NAME = ' + CHAR(39) + @TABLE2 + CHAR(39) + ' 
			EXCEPT 
			SELECT 
				COLUMN_NAME, 
				DATA_TYPE, 
				CHARACTER_MAXIMUM_LENGTH, 
				NUMERIC_PRECISION,  
				NUMERIC_PRECISION_RADIX, 
				NUMERIC_SCALE, 
				DATETIME_PRECISION, 
				IS_NULLABLE 
			FROM ' 
				+ @COLUMNS_TABLE1 + ' 
			WHERE 
				TABLE_NAME = ' + CHAR(39) + @TABLE1 + CHAR(39)

	BEGIN TRY 	
	
		EXEC(@COMMAND);

	END TRY 

	BEGIN CATCH 

		PRINT 'ERROR_MESSAGE = ' + ERROR_MESSAGE() + ' ERROR LINE = ' + STR(ERROR_LINE())
		PRINT ''

	END CATCH 

	OPEN c 
	FETCH NEXT FROM 
		c 
	INTO 
		@NAME, 
		@DATA_TYPE, 
		@CHARACTER_MAXIMUM_LENGTH, 
		@NUMERIC_PRECISION, 
		@NUMERIC_PRECISION_RADIX, 
		@NUMERIC_SCALE, 
		@DATETIME_PRECISION, 
		@IS_NULLABLE

	SET @COUNT = 1

	WHILE @@FETCH_STATUS = 0

		BEGIN 

			BEGIN TRY 

				PRINT 'FETCH ' + STR(@COUNT)
				SET @COUNT = @COUNT + 1
				PRINT  'COLUMN:  ' + 
					@NAME + ', ' + 
					@DATA_TYPE + ', ' + 
					CASE 
						WHEN @CHARACTER_MAXIMUM_LENGTH  IS NULL THEN '0' 
						ELSE 	CAST(@CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) 
					END 
					+ ', ' + 
					CASE 
						WHEN @NUMERIC_PRECISION IS NULL THEN '0' 
						ELSE CAST(@NUMERIC_PRECISION AS NVARCHAR(10)) 
					END 
					 + ', ' + 
					CASE 
						WHEN @NUMERIC_PRECISION_RADIX IS NULL THEN '0' 
						ELSE CAST(@NUMERIC_PRECISION_RADIX AS NVARCHAR(10)) 
					END 
					+ ', ' + 
					CASE 
						WHEN @NUMERIC_SCALE IS NULL THEN '0' 
						ELSE CAST(@NUMERIC_SCALE AS NVARCHAR(10)) 
					END 
					+ ', ' + 
					CASE 
						WHEN @DATETIME_PRECISION IS NULL THEN '0' 
						ELSE CAST(@DATETIME_PRECISION AS NVARCHAR(10)) 
					END 
					+ ', ' + 
					@IS_NULLABLE 
					PRINT ''

			END TRY 

			BEGIN CATCH

				PRINT 'ERROR:  ' + ERROR_MESSAGE()
 
			END CATCH 

			FETCH NEXT FROM 
				c 
			INTO 
				@NAME, 
				@DATA_TYPE, 
				@CHARACTER_MAXIMUM_LENGTH, 
				@NUMERIC_PRECISION, 
				@NUMERIC_PRECISION_RADIX, 
				@NUMERIC_SCALE, 
				@DATETIME_PRECISION, 
				@IS_NULLABLE

		END

	CLOSE c
	DEALLOCATE c