----Declaration
BEGIN
DECLARE @Status_TableNotFound INT = 1
DECLARE @Status_FieldNotFound INT = 2
DECLARE @Status_FieldTypeChnage INT = 3
DECLARE @Status_FieldLengthChange INT = 4
ENd
--Get Table / Filed List
BEGIN
SELECT
--- db1
db1Objects.id AS db1Id
, db1Objects.name AS db1TableName
, db1Column.name AS db1FieldName
, db1Column.xType as db1FieldType
, UPPER(Type_Name(db1Column.xtype)) AS db1FieldTypeName
, db1Column.length as db1FieldLength
, db1Column.ColStat & 1 AS db1IsIdentity
--- db2
, ISNULL( db2Objects.id, 0) AS db2Id
, ISNULL( db2Objects.name, '') AS db2TableName
, ISNULL( db2Column.name, '') AS db2FieldName
, ISNULL( db2Column.xType, 0 ) as db2FieldType
, UPPER(ISNULL( Type_Name(db2Column.xtype), '' )) AS db2FieldTypeName
, ISNULL( db2Column.length, 0 ) as db2FieldLength
, ISNULL( db2Column.ColStat & 1, 0 ) AS db2IsIdentity
, 0 AS Status
INTO #FieldCheck
FROM <DBName1>.dbo.SysObjects AS db1Objects
INNER JOIN <DBName1>.dbo.SysColumns AS db1Column ON db1Column.id = db1Objects.id
AND db1Objects.xtype = 'U'
AND db1Objects.category = 0
LEFT OUTER JOIN <DBName2>.dbo.SysObjects AS db2Objects ON db2Objects.name = db1Objects.name
AND db2Objects.xtype = 'U'
AND db2Objects.category = 0
LEFT OUTER JOIN <DBName2>.dbo.SysColumns AS db2Column ON db2Column.id = db2Objects.id
AND db2Column.name = db1Column.name
END
--- Check for Status
BEGIN
--- TableNotFound
UPDATE #FieldCheck
SET Status = @Status_TableNotFound
WHERE db1TableName != db2TableName
--- FieldNotFound
UPDATE #FieldCheck
SET Status = @Status_FieldNotFound
WHERE db1FieldName != db2FieldName
AND Status = 0
--- FieldTypeChnage
UPDATE #FieldCheck
SET Status = @Status_FieldTypeChnage
WHERE db1FieldName = db2FieldName
AND db1FieldTypeName != db2FieldTypeName
AND Status = 0
--- FieldLengthChange
UPDATE #FieldCheck
SET Status = @Status_FieldLengthChange
WHERE db1FieldLength != db2FieldLength
AND Status = 0
END
--- TableNotFound
SELECT DISTINCT db1TableName AS TableNotFound
FROM #FieldCheck
WHERE Status = @Status_TableNotFound
--- FieldNotFound
SELECT DISTINCT
db1TableName AS TableName
, db1FieldName FieldNotFound
, 'ALTER TABLE ' + db1TableName +
' ADD ' + db1FieldName +
' ' + db1FieldTypeName +
' ' + (
CASE
WHEN db1FieldTypeName IN ( 'VARCHAR', 'NVARCHAR' )
THEN '(' + CAST( db1FieldLength AS VARCHAR(10) ) + ')'
ELSE ''
END
) +
'; UPDATE ' + db1TableName +
' SET ' + db1FieldName + ' = ' +
(
CASE
WHEN db1FieldTypeName LIKE '%CHAR%'
OR db1FieldTypeName LIKE '%TEXT%'
OR db1FieldTypeName LIKE '%XML%'
THEN ''''''
WHEN db1FieldTypeName LIKE 'INT'
OR db1FieldTypeName LIKE 'SMALLINT'
OR db1FieldTypeName LIKE 'BIGINT'
OR db1FieldTypeName LIKE '%BINARY%'
OR db1FieldTypeName LIKE 'float'
OR db1FieldTypeName = 'BIT'
THEN '0'
ELSE 'object'
END
)
' ; '
FROM #FieldCheck
WHERE Status = @Status_FieldNotFound
--- FieldTypeChnage
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName, db2FieldTypeName
FROM #FieldCheck
WHERE Status = @Status_FieldTypeChnage
--- FieldLengthChange
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName AS FieldType, db1FieldLength, db2FieldLength
FROM #FieldCheck
WHERE Status = @Status_FieldLengthChange
DROP TABLE #FieldCheck
Monday, January 25, 2010
GET Database diffrence
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment