SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Compare schema between tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/06/2006 :  05:36:24  Show Profile  Reply with Quote
In the process of purging data to history tables,
we wanted to make sure that no schema changes have been done
to the main or the history table.
So to ensure identical schemas, we use this function:


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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/12/2006 :  22:51:37  Show Profile  Reply with Quote
Maybe it isn't important, but have you thought of adding a check to:
1. Make sure they both exist.
2. Make sure they are either both tables or both views?



CODO ERGO SUM
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/13/2006 :  02:06:21  Show Profile  Reply with Quote
1. If neither exists, it will return 1 "true", I figured 2 empty schemas are identical.
If only one exists it will return 0 "false", because of the count(*) <> 2

2. That's a point, if anyone needs that, just add TABLE_TYPE to the select list.

rockmoose

Edited by - rockmoose on 10/13/2006 02:10:57
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 10/13/2006 :  02:40:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe you also should include constraint checks and/or foreign keys?

INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/13/2006 :  03:06:47  Show Profile  Reply with Quote
You could, but in this case, we just use it to check the table schema before moving data into historization tables.
The constraints ideally would be the same on both tables, but might not be.
For instance we don't usually put FK constraints on the history tables.
For performance reasons,
and it's also a lot of extra work to move the data out of the main tables, following the relationship chain upwards/downwards.

Some of the history tables are also partitioned, which makes full FK support harder.

rockmoose
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/13/2006 :  06:29:21  Show Profile  Reply with Quote
quote:
Originally posted by rockmoose
...If neither exists, it will return 1 "true", I figured 2 empty schemas are identical...


NULL = NULL ???




CODO ERGO SUM
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/13/2006 :  07:00:01  Show Profile  Reply with Quote
It's not a 3VL function...

Could return 0, but,
I figured that if you try to insert non-existant tables, you actually might WANT an error!

rockmoose
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000