MSSQL 获取数据库字段类型

SELECT
   col.name AS 列名,
   typ.name as 数据类型,
   col.max_length AS 占用字节数,
   col.precision AS 数字长度,
   col.scale AS 小数位数,
   col.is_nullable  AS 是否允许非空,
   col.is_identity  AS 是否自增,
   case when exists 
      ( SELECT 1 
        FROM 
          sys.indexes idx 
            join sys.index_columns idxCol 
            on (idx.object_id = idxCol.object_id)
         WHERE
            idx.object_id = col.object_id
            AND idxCol.index_column_id = col.column_id
            AND idx.is_primary_key = 1
       ) THEN 1 ELSE 0 END  AS 是否是主键,
  isnull(prop.[value],'-') AS 说明
FROM
  sys.columns col 
    left join sys.types typ 
      on (col.system_type_id = typ.system_type_id)
    left join sys.extended_properties prop
      on (col.object_id = prop.major_id AND prop.minor_id = col.column_id)
WHERE
  col.object_id =
    (SELECT object_id FROM sys.tables WHERE name = 'band')