/*** 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]