【SQLServer】同一列名なのに桁数や列説明が食い違う他テーブルの列を抽出

同じ列名なのに他テーブルでは列説明が違ったり、
型が違ったり、桁数が違ったりする列ってちらほら出てきますよね。

発見するのが難しかったりするのでSQLを作成しました。

view plainprint?

1. SELECT DISTINCT  
2.   COL1.TABLE_NAME  AS テーブル名  
3. ,( select b.value from sys.tables a  
4.    left join sys.extended_properties b on a.object_id = b.major_id and a.parent_object_id = b.minor_id   
5.    where a.name =COL1.TABLE_NAME) AS テーブル説明  
6. , COL1.COLUMN_NAME AS 列名  
7. , COL1.DATA_TYPE AS 型  
8. , COL1.CHARACTER_MAXIMUM_LENGTH AS 文字桁  
9. , COL1.NUMERIC_PRECISION AS 整数桁  
10. , COL1.NUMERIC_SCALE AS 小数桁  
11. , COL1.IS_NULLABLE AS NULL許容  
12. , COMMENT.COLUMN_COMMENT AS 列説明  
13.   
14. FROM INFORMATION_SCHEMA.COLUMNS COL1  
15. -- コメントを取得  
16. INNER JOIN(  
17. select   
18.      t.name as TABLE_NAME  
19.     ,c.name as COLUMN_NAME  
20.     ,ep.value as COLUMN_COMMENT  
21. from  
22.       sys.tables              t  
23.      ,sys.columns             c  
24.      ,sys.extended_properties ep  
25. where  
26.       t.object_id = c.object_id  
27. and c.object_id = ep.major_id  
28. and c.column_id = ep.minor_id) COMMENT  
29. ON COMMENT.TABLE_NAME = COL1.TABLE_NAME  
30. AND COMMENT.COLUMN_NAME = COL1.COLUMN_NAME  
31.   
32. -- 列の情報が食い違うもの  
33. LEFT JOIN INFORMATION_SCHEMA.COLUMNS COL2  
34. ON COL1.COLUMN_NAME = COL2.COLUMN_NAME   
35. AND (COL1.DATA_TYPE <> COL2.DATA_TYPE   
36.   OR COL1.CHARACTER_MAXIMUM_LENGTH <> COL2.CHARACTER_MAXIMUM_LENGTH   
37.   OR COL1.NUMERIC_PRECISION <> COL2.NUMERIC_PRECISION   
38.   OR COL1.NUMERIC_SCALE  <> COL2.NUMERIC_SCALE   
39.   )  
40. WHERE   
41. (  
42.        -- 列のコメントが食い違うもの  
43.        (  
44.               select  COUNT(distinct ep.value )--c.name AS COLUMN_NAME  
45.   
46.               from  
47.                        sys.tables              t  
48.                      ,sys.columns             c  
49.                      ,sys.extended_properties ep  
50.               where  
51.                      t.object_id = c.object_id  
52.               and c.object_id = ep.major_id  
53.               and c.column_id = ep.minor_id  
54.               and c.name = COL1.COLUMN_NAME   
55.        group by c.name  
56.        ) > 1  
57.        -- 列の情報が食い違うもの　のJOIN がヒットした  
58. OR COL2.COLUMN_NAME IS NOT NULL  
59. )  
60.   
61. -- 列名指定する場合はここに条件  
62. --WHERE COL1.COLUMN_NAME = 'HIN_CD'  
63.   
64. ORDER BY COL1.COLUMN_NAME ASC  

SELECT DISTINCT
  COL1.TABLE_NAME  AS テーブル名
,( select b.value from sys.tables a
   left join sys.extended_properties b on a.object_id = b.major_id and a.parent_object_id = b.minor_id 
   where a.name =COL1.TABLE_NAME) AS テーブル説明
, COL1.COLUMN_NAME AS 列名
, COL1.DATA_TYPE AS 型
, COL1.CHARACTER_MAXIMUM_LENGTH AS 文字桁
, COL1.NUMERIC_PRECISION AS 整数桁
, COL1.NUMERIC_SCALE AS 小数桁
, COL1.IS_NULLABLE AS NULL許容
, COMMENT.COLUMN_COMMENT AS 列説明

FROM INFORMATION_SCHEMA.COLUMNS COL1
-- コメントを取得
INNER JOIN(
select 
     t.name as TABLE_NAME
    ,c.name as COLUMN_NAME
    ,ep.value as COLUMN_COMMENT
from
      sys.tables              t
     ,sys.columns             c
     ,sys.extended_properties ep
where
      t.object_id = c.object_id
and c.object_id = ep.major_id
and c.column_id = ep.minor_id) COMMENT
ON COMMENT.TABLE_NAME = COL1.TABLE_NAME
AND COMMENT.COLUMN_NAME = COL1.COLUMN_NAME

-- 列の情報が食い違うもの
LEFT JOIN INFORMATION_SCHEMA.COLUMNS COL2
ON COL1.COLUMN_NAME = COL2.COLUMN_NAME 
AND (COL1.DATA_TYPE <> COL2.DATA_TYPE 
  OR COL1.CHARACTER_MAXIMUM_LENGTH <> COL2.CHARACTER_MAXIMUM_LENGTH 
  OR COL1.NUMERIC_PRECISION <> COL2.NUMERIC_PRECISION 
  OR COL1.NUMERIC_SCALE  <> COL2.NUMERIC_SCALE 
  )
WHERE 
(
       -- 列のコメントが食い違うもの
       (
              select  COUNT(distinct ep.value )--c.name AS COLUMN_NAME

              from
                       sys.tables              t
                     ,sys.columns             c
                     ,sys.extended_properties ep
              where
                     t.object_id = c.object_id
              and c.object_id = ep.major_id
              and c.column_id = ep.minor_id
              and c.name = COL1.COLUMN_NAME 
       group by c.name
       ) > 1
       -- 列の情報が食い違うもの　のJOIN がヒットした
OR COL2.COLUMN_NAME IS NOT NULL
)

-- 列名指定する場合はここに条件
--WHERE COL1.COLUMN_NAME = 'HIN_CD'

ORDER BY COL1.COLUMN_NAME ASC

実行すると修正の必要性がある列のみ抽出されます。

Oracleでも似たようなの作っておくと便利！

※ちなみにSQLはインデントなどちゃんと直してません。