Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Vergleich von Tabellen / Sichten + Felder über Datenbanken und Server

12. August 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Die Microsoft SQL Server 2008 R2 August CTP steht an. In der BOL zur CTP steht unter „New Features [SQL Server]“ zwar, das es eigentlich keine Änderungen / Neuerungen am Datenbank-Modul gab, das heißt aber nicht auch, das nicht Änderungen an den DMV (Dynamic Management Views) vorgenommen wurde.
Nun könnte man die neue BOL nach Änderungen an den DMVs durchstöbern, wäre aber etwas (zeit-) aufwendig. Einfacher ist es, abzuwarten, was andere dazu schreiben ;-)
Aber es geht auch besser, schneller & genauer und zwar indem man die Designs direkt mit einander vergleicht. Das geht eigentlich ganz einfach:
Zunächst habe ich einen „Linked Server“ namens „DEST“ angelegt. Das Selektieren der Tabellen / Sichten – Objektdaten mit den dazu gehörenden Felder dürfte ja bekannt sein. Um auch wirklich alle System-Objekte zu erhalten, werde ich dazu sys.sysobjects und sys.all_columns statt wie sonst sys.objects / sys.columns. Die Selektion nehme ich auf beiden Seiten vor, lokal und remote, und verbinde sie per FULL OUTER JOIN, um auch neue & gelöschte Objekte zu bekommen
Die IDs der Objekte und Felder kann/sollte man nicht wirklich verwenden, um beide Seiten in Beziehung zu setzen; die können voneinander abweichen. Die Objektnamen sind aber selbst auch eindeutig, also kann man auch die Verwenden. Um Probleme mit unterschiedlichen Collations vorzubeugen, caste ich Collation dabei jeweils.
Bevor einer fragt: Der Punkt in JOIN .[master].sys.all_columns vor [master] ist kein Tippfehler, sondern pure Absicht, damit ich im Skript nicht versehentlich die Tabellen aus der falschen Instanz anspreche (wo do DB und Tabellennamen gleich sind).

 Natürlich kann man das T-SQL Skript auch für jede andere beliebige Datenbank verwenden, man muss nur den Datenbank-Namen anpassen. 
 -- Table / View Design Compare
-- Vergleich von Tabellen / Sichten + Felder über Datenbanken und Server hinweg
-- Olaf Helper, 12.08.2009
SELECT CASE WHEN dOBJ.name IS NULL THEN 'New Tbl'
WHEN sOBJ.name IS NULL THEN 'Tbl drop'
ELSE '' END AS ObjChanges,
CASE WHEN dCOL.name IS NULL AND NOT dOBJ.name IS NULL THEN 'New Col'
WHEN sCOL.name IS NULL AND NOT sOBJ.name IS NULL
AND NOT dCOL.name IS NULL THEN 'Col drop'
WHEN dTYP.name COLLATE SQL_Latin1_General_CP1_CI_AS
<> sTYP.name COLLATE SQL_Latin1_General_CP1_CI_AS
THEN 'Type changed'
ELSE '' END AS ColTypeChanges,
CASE WHEN dCOL.max_length <> sCOL.max_length
OR dCOL.precision <> sCOL.precision
OR dCOL.scale <> sCOL.scale
THEN 'Len/Prec/Scale changed'
ELSE '' END AS ColLenChanges,
ISNULL(dOBJ.type, sOBJ.type) AS ObjType,
ISNULL(dOBJ.name, sOBJ.name) AS ObjName,
ISNULL(dCOL.name, sCOL.name) AS ColName,
dTYP.name AS DstColType, sTYP.name AS SrcColType,
dCOL.max_length AS DstColLen, sCOL.max_length AS SrcColLen,
dCOL.precision AS DstColPrec, sCOL.precision AS SrcColPrec,
dCOL.scale AS DstColScale, sCOL.scale AS SrcColScale,
-- Nicht so interessant, deshalb ohne Änderungshinweis
dCOL.is_identity AS DstIdent, sCOL.is_identity AS SrcIdent,
dCOL.is_nullable AS DstNull, sCOL.is_nullable AS SrcNull,
dCOL.is_xml_document AS DstXml, sCOL.is_xml_document AS SrcXml,
dCOL.collation_name AS DstCollation, sCOL.collation_name AS SrcCollation
FROM [DEST].[master].sys.sysobjects AS dOBJ
LEFT JOIN [DEST].[master].sys.all_columns as dCOL
ON dOBJ.id = dCOL.object_id
LEFT JOIN [DEST].[master].sys.types AS dTYP
ON dCOL.system_type_id = dTYP.system_type_id
AND dCOL.user_type_id = dTYP.user_type_id
FULL OUTER JOIN .[master].sys.sysobjects AS sOBJ
ON dOBJ.type COLLATE SQL_Latin1_General_CP1_CI_AS
= sOBJ.type COLLATE SQL_Latin1_General_CP1_CI_AS
AND dOBJ.name COLLATE SQL_Latin1_General_CP1_CI_AS
= sOBJ.name COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT JOIN .[master].sys.all_columns as sCOL
ON sOBJ.id = sCOL.object_id
AND dCOL.name COLLATE SQL_Latin1_General_CP1_CI_AS
= sCOL.name COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT JOIN .[master].sys.types AS sTYP
ON sCOL.system_type_id = sTYP.system_type_id
AND sCOL.user_type_id = sTYP.user_type_id
AND dTYP.name COLLATE SQL_Latin1_General_CP1_CI_AS
= sTYP.name COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE ISNULL(dOBJ.type, '') IN ('S ', 'U ', 'V ', '')
AND ISNULL(sOBJ.type, '') IN ('S ', 'U ', 'V ', '')
/* Enkommentieren, um nur Änderungen aufzuführen !
AND (dOBJ.name IS NULL
OR sOBJ.name IS NULL
OR dCOL.name IS NULL
OR sCOL.name IS NULL
OR dTYP.name COLLATE SQL_Latin1_General_CP1_CI_AS
<> sTYP.name COLLATE SQL_Latin1_General_CP1_CI_AS
OR dCOL.max_length <> sCOL.max_length
OR dCOL.precision <> sCOL.precision
OR dCOL.scale <> sCOL.scale
OR dCOL.is_identity <> sCOL.is_identity
OR dCOL.is_nullable <> sCOL.is_nullable
OR dCOL.is_xml_document <> sCOL.is_xml_document
OR dCOL.collation_name <> sCOL.collation_name
) */
ORDER BY ISNULL(dOBJ.type, sOBJ.type),
ISNULL(dOBJ.name, sOBJ.name),
ISNULL(dCOL.name, sCOL.name)

Diesen Post teilen

Repost 0

Kommentiere diesen Post