比對兩個結構一樣的資料庫


並將有差異的部份列出


例如: 欄位長度, 大小, 缺少的TABLE等


以下的做法不是最少, 但至少...還能達到要求


USE master


CREATE TABLE #TmpLTColumn
(
 TABLE_CATALOG nvarchar(128),
 TABLE_SCHEMA nvarchar(128),
 TABLE_NAME nvarchar(128),
 TABLE_TYPE varchar(10),
 COLUMN_NAME nvarchar(128),
 COLUMN_DEFAULT nvarchar(4000),
 IS_NULLABLE varchar(3),
 DATA_TYPE  nvarchar(128),
 CHARACTER_MAXIMUM_LENGTH  int,
 NUMERIC_PRECISION  tinyint,
 NUMERIC_PRECISION_RADIX  smallint ,
 NUMERIC_SCALE  int,
 COLLATION_NAME  nvarchar(128),
 IsIdentity int
)


CREATE TABLE #TmpKSColumn
(
 TABLE_CATALOG nvarchar(128),
 TABLE_SCHEMA nvarchar(128),
 TABLE_NAME nvarchar(128),
 TABLE_TYPE varchar(10),
 COLUMN_NAME nvarchar(128),
 COLUMN_DEFAULT nvarchar(4000),
 IS_NULLABLE varchar(3),
 DATA_TYPE  nvarchar(128),
 CHARACTER_MAXIMUM_LENGTH  int,
 NUMERIC_PRECISION  tinyint,
 NUMERIC_PRECISION_RADIX  smallint ,
 NUMERIC_SCALE  int,
 COLLATION_NAME  nvarchar(128),
 IsIdentity int
)


INSERT INTO #TmpLTColumn
SELECT
 A.TABLE_CATALOG, A.TABLE_SCHEMA, A.TABLE_NAME, A.TABLE_TYPE, B.COLUMN_NAME, B.COLUMN_DEFAULT, B.IS_NULLABLE,
 B.DATA_TYPE, B.CHARACTER_MAXIMUM_LENGTH, B.NUMERIC_PRECISION, B.NUMERIC_PRECISION_RADIX, B.NUMERIC_SCALE,
 B.COLLATION_NAME, columnproperty(object_id(A.TABLE_NAME), B.COLUMN_NAME, 'IsIdentity') IsIdentity
FROM WFE98PLUS.information_schema.Tables A
 JOIN WFE98PLUS.information_schema.COLUMNS B ON A.TABLE_CATALOG = B.TABLE_CATALOG
           AND A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_NAME NOT IN ('員工_Ini','員工_old','員工1','部門_Ini','部門_old')
ORDER BY B.TABLE_CATALOG, B.TABLE_NAME, B.ORDINAL_POSITION


INSERT INTO #TmpKSColumn
SELECT
 A.TABLE_CATALOG, A.TABLE_SCHEMA, A.TABLE_NAME, A.TABLE_TYPE, B.COLUMN_NAME, B.COLUMN_DEFAULT, B.IS_NULLABLE,
 B.DATA_TYPE, B.CHARACTER_MAXIMUM_LENGTH, B.NUMERIC_PRECISION, B.NUMERIC_PRECISION_RADIX, B.NUMERIC_SCALE,
 B.COLLATION_NAME, columnproperty(object_id(A.TABLE_NAME), B.COLUMN_NAME, 'IsIdentity') IsIdentity
FROM KNSI.information_schema.Tables A
 JOIN KNSI.information_schema.COLUMNS B ON A.TABLE_CATALOG = B.TABLE_CATALOG
           AND A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_NAME NOT IN ('員工_Ini','員工_old','員工1','部門_Ini','部門_old')
ORDER BY B.TABLE_CATALOG, B.TABLE_NAME, B.ORDINAL_POSITION


SELECT
 TABLE_NAME = (CASE ISNULL(A.TABLE_NAME,'')
      WHEN '' THEN B.TABLE_NAME
      ELSE  A.TABLE_NAME END),
 TABLE_TYPE = (CASE ISNULL(A.TABLE_TYPE,'')
      WHEN '' THEN B.TABLE_TYPE
      ELSE  A.TABLE_TYPE END),
 COLUMN_NAME = (CASE ISNULL(A.COLUMN_NAME,'')
      WHEN '' THEN B.COLUMN_NAME
      ELSE  A.COLUMN_NAME END),
 '異常狀況' = CASE
     WHEN ISNULL(A.TABLE_CATALOG,'') = '' THEN '龍騰缺此欄位'
     WHEN ISNULL(B.TABLE_CATALOG,'') = '' THEN '康熹缺此欄位'
     WHEN A.CHARACTER_MAXIMUM_LENGTH <> B.CHARACTER_MAXIMUM_LENGTH THEN '欄位長度 : 龍騰(' + CONVERT(VARCHAR(10),A.CHARACTER_MAXIMUM_LENGTH) + '), 康熹(' + CONVERT(VARCHAR(10),B.CHARACTER_MAXIMUM_LENGTH) + ')'
     WHEN A.TABLE_SCHEMA <> B.TABLE_SCHEMA THEN 'TABLE_SCHEMA : 龍騰(' + CONVERT(VARCHAR(10),A.TABLE_SCHEMA) + '), 康熹(' + CONVERT(VARCHAR(10),B.TABLE_SCHEMA) + ')'
     WHEN A.TABLE_TYPE <> B.TABLE_TYPE THEN 'TABLE定義 : 龍騰(' + CONVERT(VARCHAR(10),A.TABLE_TYPE) + '), 康熹(' + CONVERT(VARCHAR(10),B.TABLE_TYPE) + ')'
     WHEN A.COLUMN_DEFAULT <> B.COLUMN_DEFAULT THEN '預設值 : 龍騰(' + CONVERT(VARCHAR(10),A.COLUMN_DEFAULT) + '), 康熹(' + CONVERT(VARCHAR(10),B.COLUMN_DEFAULT) + ')'
     WHEN A.IS_NULLABLE <> B.IS_NULLABLE THEN 'IsNull : 龍騰(' + CONVERT(VARCHAR(10),A.IS_NULLABLE) + '), 康熹(' + CONVERT(VARCHAR(10),B.IS_NULLABLE) + ')'
     WHEN A.DATA_TYPE <> B.DATA_TYPE THEN '欄位型態 : 龍騰(' + CONVERT(VARCHAR(10),A.DATA_TYPE) + '), 康熹(' + CONVERT(VARCHAR(10),B.DATA_TYPE) + ')' 
     WHEN A.NUMERIC_PRECISION <> B.NUMERIC_PRECISION THEN 'NUMERIC_PRECISION : 龍騰(' + CONVERT(VARCHAR(10),A.NUMERIC_PRECISION) + '), 康熹(' + CONVERT(VARCHAR(10),B.NUMERIC_PRECISION) + ')'
     WHEN A.NUMERIC_PRECISION_RADIX <> B.NUMERIC_PRECISION_RADIX THEN 'NUMERIC_PRECISION_RADIX : 龍騰(' + CONVERT(VARCHAR(10),A.NUMERIC_PRECISION_RADIX) + '), 康熹(' + CONVERT(VARCHAR(10),B.NUMERIC_PRECISION_RADIX) + ')'
     WHEN A.NUMERIC_SCALE <> B.NUMERIC_SCALE THEN 'NUMERIC_SCALE : 龍騰(' + CONVERT(VARCHAR(10),A.NUMERIC_SCALE) + '), 康熹(' + CONVERT(VARCHAR(10),B.NUMERIC_SCALE) + ')'
     WHEN A.COLLATION_NAME <> B.COLLATION_NAME THEN '定序 : 龍騰(' + CONVERT(VARCHAR(10),A.COLLATION_NAME) + '), 康熹(' + CONVERT(VARCHAR(10),B.COLLATION_NAME) + ')'
     WHEN A.IsIdentity <> B.IsIdentity THEN 'IsIdentity : 龍騰(' + CONVERT(VARCHAR(10),A.IsIdentity) + '), 康熹(' + CONVERT(VARCHAR(10),B.IsIdentity) + ')'
    END 
FROM #TmpLTColumn A
FULL JOIN #TmpKSColumn B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
WHERE ISNULL(A.TABLE_CATALOG, '') = '' OR
  ISNULL(B.TABLE_CATALOG, '') = '' OR
  (A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND
   (A.TABLE_SCHEMA <> B.TABLE_SCHEMA OR
   A.TABLE_TYPE  <>  B.TABLE_TYPE OR
   A.COLUMN_DEFAULT  <> B.COLUMN_DEFAULT OR
   A.IS_NULLABLE  <> B.IS_NULLABLE OR
   A.DATA_TYPE   <> B.DATA_TYPE  OR
   A.CHARACTER_MAXIMUM_LENGTH   <> B.CHARACTER_MAXIMUM_LENGTH OR
   A.NUMERIC_PRECISION   <> B.NUMERIC_PRECISION OR
   A.NUMERIC_PRECISION_RADIX   <> B.NUMERIC_PRECISION_RADIX  OR
   A.NUMERIC_SCALE   <> B.NUMERIC_SCALE OR
   A.COLLATION_NAME  <> B.COLLATION_NAME OR
   A.IsIdentity  <> B.IsIdentity )
  )
ORDER BY TABLE_NAME, COLUMN_NAME


DROP TABLE #TmpLTColumn
DROP TABLE #TmpKSColumn



 

nelman 發表在 痞客邦 PIXNET 留言(0) 人氣()