比對資料庫欄位的SQL SCRIPT－nelman

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

並將有差異的部份列出

例如: 欄位長度, 大小, 缺少的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