SQLSRV001 - やするん＠Wiki

テーブルの列情報を取得する

SELECT
    COL.column_id,
    left(COL.name,20) as Column_Name,
    left(TYP.name,20) as Column_Type,
    COL.max_length,
      (CASE COL.is_nullable
         WHEN 0 THEN 'NO'
         WHEN 1 THEN 'YES'
         ELSE 'nothing'
      END) as 'NULL可',
    substring(convert(varchar, ep.value), 1, 20)  as COMMENT
FROM
    sys.objects AS OBJ,
    sys.columns AS COL,
    sys.types AS TYP,
    sys.extended_properties ep
WHERE
    OBJ.name = 'テーブル名'
AND OBJ.object_id = COL.object_id
AND COL.user_type_id = TYP.user_type_id
AND OBJ.object_id = ep.major_id
AND COL.column_id = ep.minor_id
ORDER BY
    COL.column_id;
GO

…OracleやMySQLだと

DESC 'テーブル名'

で済んじゃうのに、えらい長くなるなぁ。

---