[MSSQL] (命令)列出所有表.字段名.主键.类型.长度.小数位数等信息

注：来源于网络

-- ===========================================================
-- 列出 SQL SERVER 所有表,字段名,主键,类型,长度,小数位数等信息 
-- 在查询分析器里运行即可 
-- ===========================================================
select
  (case
     when a.colorder = 1 then d.name
     else ''
   end)                                          as '表名',
  a.colorder                                     as '字段序号',
  a.name                                         as '字段名',
  (case
     when columnproperty(a.id,a.name,'IsIdentity') = 1 then '√'
     else ''
   end)                                          as '标识',
  (case
     when (select count(*) from sysobjects
             where (name  in (select name  from sysindexes   where id = a.id
               and (indid in (select indid from sysindexkeys where id = a.id
               and (colid in (select colid from syscolumns   where id = a.id and name = a.name))))))
      and xtype = 'PK') > 0 then '√'
     else ''
   end)                                          as '主键',
  b.name                                         as '类型',
  a.length                                       as '占用字节数',
  columnproperty(a.id,a.name,'PRECISION')        as '长度',
  isnull(columnproperty(a.id,a.name,'Scale'),0)  as '小数位数',
  (case
     when a.isnullable = 1 then '√'
     else ''
   end)                                          as '允许空',
  isnull(e.text,'')                              as '默认值',
  isnull(g.value,'')                             as '字段说明'
from syscolumns a
  left  join systypes      b on a.xtype = b.xusertype
  inner join sysobjects    d on a.id = d.id and d.xtype = 'U' and d.name <> 'dtproperties'
  left  join syscomments   e on a.cdefault = e.id
  left  join sysproperties g on a.id = g.id and a.colid = g.smallid
order by a.id,a.colorder