【SQL Server】保守・調査で役立つ定番SQLまとめ｜テーブル定義・関数・ストアド・Viewの確認方法

SELECT @@version

-- テーブル一覧と列定義
SELECT * FROM (
  SELECT
    (SELECT name FROM sys.schemas WHERE T.schema_id = schema_id) AS スキーマ,
    T.name AS 表名,
    C.column_id AS 列番,
    C.name AS 列名,
    Y.name AS 型,
    CASE
      WHEN Y.name IN ('nvarchar', 'nchar') THEN C.max_length / 2
      WHEN C.precision = 0 THEN C.max_length
      ELSE C.precision
    END AS 桁,
    C.scale AS 小数桁,
    C.max_length AS サイズ(バイト),
    C.is_nullable AS Null可,
    object_definition(C.default_object_id) AS デフォルト値,
    (SELECT COUNT(*) FROM sys.index_columns ic
     WHERE ic.column_id = C.column_id AND C.object_id = ic.object_id
     AND EXISTS (
       SELECT * FROM sys.key_constraints kc
       WHERE kc.type = 'PK'
       AND kc.parent_object_id = T.object_id
       AND kc.unique_index_id = ic.index_id
     )) AS PK,
    ep.value AS 説明
  FROM sys.tables AS T
  INNER JOIN sys.columns AS C ON T.object_id = C.object_id
  INNER JOIN sys.types AS Y ON C.system_type_id = Y.system_type_id AND C.user_type_id = Y.user_type_id
  LEFT OUTER JOIN sys.extended_properties AS ep ON C.object_id = ep.major_id AND C.column_id = ep.minor_id
) z
WHERE z.表名 NOT LIKE ('除外したいテーブル')
ORDER BY z.スキーマ, z.表名, z.列番

-- 共通関数一覧と更新日
SELECT name,
       type,
       create_date,
       modify_date
FROM sys.objects
WHERE type = 'FN'
ORDER BY modify_date

-- ストアドプロシージャ一覧
SELECT name,
       create_date,
       modify_date
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date

-- View一覧
SELECT name,
       create_date,
       modify_date
FROM sys.objects
WHERE type = 'V'
ORDER BY modify_date