Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Find Schematic Differences

Author  Topic 

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 08:24:36
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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-03 : 08:50:29
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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 08:59:27
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-03 : 09:33:26
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
   

- Advertisement -