ALTER FUNCTION dbo.fnCompareTableSchema
(
	@t1Name NVARCHAR(257)
	,@t2Name NVARCHAR(257)
)
RETURNS BIT
AS
/*
	Compares the schema of 2 tables
	If the schema is different RETURNS 0
	If the schema is identical between the two table, RETURNS 1
	NOTE: system tables or non-existant tables that are NOT in INFORMATION_SCHEMA views will compare equal (RETURNS 1)
	==================================================================================================================
	SAMPLE USAGE:
	DECLARE @schemaOK BIT
	SELECT @schemaOK = dbo.fnCompareTableSchema('dbo.table1','dbo.table2')
	
	IF @schemaOK = 1
		PRINT 'TABLE SCHEMA IDENTICAL'
	ELSE
		PRINT 'TABLE SCHEMA DIFFERENT'
	==================================================================================================================
*/
BEGIN
	IF @t1Name = @t2Name
	RETURN 1
	
	-- check if schema is different
	IF EXISTS
	(
		SELECT	*
		FROM
		(
			SELECT	COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
				, COLUMN_DEFAULT, IS_NULLABLE
				, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
				, COLLATION_NAME
			FROM	INFORMATION_SCHEMA.COLUMNS
			WHERE	TABLE_SCHEMA = COALESCE(PARSENAME(@t1Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t1Name,1)
			UNION ALL
			SELECT	COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
				, COLUMN_DEFAULT, IS_NULLABLE
				, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
				, COLLATION_NAME
			FROM	INFORMATION_SCHEMA.COLUMNS
			WHERE	TABLE_SCHEMA = COALESCE(PARSENAME(@t2Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t2Name,1)
		) U
		GROUP BY
			COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
		HAVING COUNT(*) <> 2
	)
	RETURN 0

	-- schema identical
	RETURN 1
END

quote:

---

Originally posted by rockmoose
...If neither exists, it will return 1 "true", I figured 2 empty schemas are identical...

---