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
 Find Schematic Differences
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/03/2009 :  08:24:36  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Don't know if anyone else will ever find this useful but here's a script to find schematic differences between one template db and other db's on the box. The dbMask is just a LIKE comparison.

The reason I wrote this is that our latest build was failing against only one db and we had no idea why. Turns out that someone had renamed one of the tables to 'c' by accident using management studio.

NB -- Added
Flags:

@parentDb -- the name of the database which will form the template for comparison.

@childMask -- a string that will be compared via LIKE to find target databases to compare with. Example '[_]%' will compare the @parentDb against all databases on the server starting with the underscore character '_'

@includeUnderscore -- a bit. Set this to 0 if you want to exclude any tables that start with the underscore character from the comparison (we use _<tablename> for staging tables)

@showSql -- set to 1 if you want a print of the dynamic sql executed

@debug -- set to 1 if you want to output the schema of the parent db as well as the changes list


/*** Check for Schematic Differences ******************************************
**
** Check schema of children against target parent schema
**
** Checks, Names, dataTypes, ordinal Position and Collation of columns
**
** Charlie (2009-Dec-03)
**
******************************************************************************/

DECLARE @parentDb VARCHAR(255)         SET @parentDb = ''
DECLARE @childMask VARCHAR(255)        SET @childMask = '[_]%'

DECLARE @includeUnderscore BIT         SET @includeUnderscore = 0

DECLARE @showSql BIT                   SET @showSql = 0
DECLARE @debug BIT                     SET @debug = 1

/*****************************************************************************/

SET NOCOUNT ON

IF OBJECT_ID('tempDb..#parentSchema') IS NOT NULL DROP TABLE #parentSchema
IF OBJECT_ID('tempDb..#results') IS NOT NULL DROP TABLE #results

DECLARE @sql NVARCHAR(MAX)
DECLARE @dbName VARCHAR(255)

CREATE TABLE #parentSchema (
	[table_schema] VARCHAR(50)
	, [table_name] VARCHAR(512)
	, [column_name] VARCHAR(512)
	, [ordinal_position] INT
	, [data_type] VARCHAR(50)
	, [character_maximum_length] INT
	, [collation_name] VARCHAR(255)
	)

CREATE TABLE #results (
	[dbName] VARCHAR(255)
	, [table_schema] VARCHAR(50)
	, [table_name] VARCHAR(512)
	, [column_name] VARCHAR(512)
	, [ordinal_position] INT
	, [data_type] VARCHAR(50)
	, [character_maximum_length] INT
	, [collation_name] VARCHAR(255)
	, [missing] BIT DEFAULT 0
	, [nameSchemaDifference] BIT DEFAULT 0
	, [positionDifference] BIT DEFAULT 0
	, [dataTypeDifference] BIT DEFAULT 0
	, [charMaxDifference] BIT DEFAULT 0
	, [collationDifference] BIT DEFAULT 0
	)
-- Populate the parent Schema
SET @Sql = N'
INSERT INTO #parentSchema (
	[table_schema]
	, [table_name]
	, [column_name]
	, [ordinal_position]
	, [data_type]
	, [character_maximum_length]
	, [collation_name]
	)
SELECT
	[table_schema]
	, [table_name]
	, [column_name]
	, [ordinal_position]
	, [data_type]
	, ISNULL([character_maximum_length], -1)
	, ISNULL([collation_name], ''N/A'')
FROM
	' + QUOTENAME(@parentDb) + '.information_schema.columns
WHERE
	[table_name] NOT LIKE ''[_]%''
	OR
	@includeUnderscore = 1
	'

IF @showSql = 1 PRINT @sql
EXEC sp_executeSql
	@sql
	, N'@includeUnderscore BIT'
	, @includeUnderscore

IF @debug = 1 SELECT * FROM #parentSchema ORDER BY
	[table_schema]
	, [table_name]
	, [column_name]
	, [ordinal_position]
	, [data_type]
	, [character_maximum_length]
	, [collation_name]

-- Cursor through databases and insert into #results where there is a difference
DECLARE dbCursor CURSOR LOCAL READ_ONLY FOR SELECT
	[name]
FROM
	sys.databases
WHERE
	[name] LIKE @childMask
	AND [name] <> @parentDb

OPEN dbCursor

	FETCH NEXT FROM dbCursor INTO @dbName

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		RAISERROR(@dbName, 0, 1) WITH NOWAIT
		SET @sql = N'
		USE ' + QUOTENAME(@dbName) + '

		-- Find extra tables / columns and datatype changes
		INSERT #results (
			[dbName]
			, [table_schema]
			, [table_name]
			, [column_name]
			, [ordinal_position]
			, [data_type]
			, [character_maximum_length]
			, [collation_name]
			)
		SELECT
			DB_NAME()
			, isc.[table_schema]
			, isc.[table_name]
			, isc.[column_name]
			, isc.[ordinal_position]
			, isc.[data_type]
			, isc.[character_maximum_length]
			, isc.[collation_name]
		FROM
			(
			SELECT
				[table_schema] AS [table_schema]
				, [table_name] AS [table_name]
				, [column_name] AS [column_name]
				, [ordinal_position] AS [ordinal_position]
				, [data_type] AS [data_type]
				, ISNULL([character_maximum_length], -1) AS [character_maximum_length]
				, ISNULL([collation_name], ''N/A'') AS [collation_name]
			FROM
				information_schema.columns
			WHERE
				[table_name] NOT LIKE ''[_]%''
				OR
				@includeUnderscore = 1
			)
			isc
			LEFT JOIN #parentSchema ps ON
				isc.[table_schema] = ps.[table_schema] COLLATE DATABASE_DEFAULT
				AND isc.[table_name] = ps.[table_name] COLLATE DATABASE_DEFAULT
				AND isc.[column_name] = ps.[column_name] COLLATE DATABASE_DEFAULT
				AND isc.[ordinal_position] = ps.[ordinal_position]
				AND isc.[data_type] = ps.[data_type] COLLATE DATABASE_DEFAULT
				AND isc.[character_maximum_length] = ps.[character_maximum_length]
				AND isc.[collation_name] = ps.[collation_name] COLLATE DATABASE_DEFAULT
		WHERE
			ps.[table_schema] IS NULL

		-- Find missing columns / tables
		INSERT #results (
			[dbName]
			, [table_schema]
			, [table_name]
			, [column_name]
			, [ordinal_position]
			, [data_type]
			, [character_maximum_length]
			, [collation_name]
			, [missing]
			)
		SELECT
			DB_NAME()
			, ps.[table_schema]
			, ps.[table_name]
			, ps.[column_name]
			, ps.[ordinal_position]
			, ps.[data_type]
			, ps.[character_maximum_length]
			, ps.[collation_name]
			, 1
		FROM
			#parentSchema ps
		WHERE
			NOT EXISTS (
				SELECT 1
				FROM information_schema.columns isc
				WHERE
					isc.[table_schema] = ps.[table_schema] COLLATE DATABASE_DEFAULT
					AND isc.[table_name] = ps.[table_name] COLLATE DATABASE_DEFAULT
					AND isc.[column_name] = ps.[column_name] COLLATE DATABASE_DEFAULT
				)
		'

		IF @showSql = 1 PRINT @Sql

		EXEC sp_executeSql
			@sql
			, N'@includeUnderscore BIT'
			, @includeUnderscore

		FETCH NEXT FROM dbCursor INTO @dbName
	END

CLOSE dbCursor
DEALLOCATE dbCursor


-- Work out why the entries are here
UPDATE r SET
	[nameSchemaDifference] = 1
FROM
	#results r
	LEFT JOIN #parentSchema ps ON
		ps.[table_schema] = r.[table_schema]
		AND ps.[table_name] = r.[table_name]
		AND ps.[column_name] = r.[column_name]
	WHERE
		ps.[table_schema] IS NULL

UPDATE r SET
	[positionDifference] = CASE WHEN r.[ordinal_position] <> ps.[ordinal_position] THEN 1 ELSE 0 END
	, [dataTypeDifference] = CASE WHEN r.[data_type] <> ps.[data_type] THEN 1 ELSE 0 END
	, [charMaxDifference] = CASE WHEN r.[character_maximum_length] <> ps.[character_maximum_length] THEN 1 ELSE 0 END
	, [collationDifference] = CASE WHEN r.[collation_name] <> ps.[collation_name] THEN 1 ELSE 0 END
FROM
	#results r
	JOIN #parentSchema ps ON
		ps.[table_schema] = r.[table_schema]
		AND ps.[table_name] = r.[table_name]
		AND ps.[column_name] = r.[column_name]

-- Display the results
SELECT *
FROM
	#results
 ORDER BY
	[dbName]
	, [table_schema]
	, [table_name]
	, [column_name]
	, [ordinal_position]
	, [data_type]
	, [character_maximum_length]
	, [collation_name]

Enjoy.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 12/03/2009 08:58:26

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 12/03/2009 :  08:50:29  Show Profile  Visit webfred's Homepage  Reply with Quote
Hi Charlie!
I think this is fine and I have had situations where I think this script was nice to have!

I will give it a try when I have the time to do that.

But there is one thing:
In the headlines of your script I would love to have a bit more dokumentation on how to use it right.
Is it possible or do I have to examine the code to get an idea what's going on depending on what values I have set the variables to?

Thank you!

Fred


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/03/2009 :  08:59:27  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Sorry webfred -- good point. Have changed my original post.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 12/03/2009 :  09:33:26  Show Profile  Visit webfred's Homepage  Reply with Quote
works fine

Thank you.


No, you're never too old to Yak'n'Roll if you're too young to die.
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.08 seconds. Powered By: Snitz Forums 2000