列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息

-- ======================================================

--列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息

--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中

-- ======================================================

SELECT

       (case when a.colorder=1 then d.name else '' end)表名,

       a.colorder 字段序号,

       a.name 字段名,

       (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,

       (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) 主键,

       b.name 类型,

       a.length 占用字节数,

       COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,

       isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,

       (case when a.isnullable=1 then '√'else '' end) 允许空,

       isnull(e.text,'') 默认值,

       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

------------------------------------------------------------------------------------------------- 

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1626544

<script src="http://localhost:82/PromoteIcon.aspx?id=1626544" type="text/javascript"></script> [ 收藏到我的网摘]   [ 发送Trackback]  dickc发表于 2007年05月26日 10:36:00

<script type="text/javascript">function hide(){showComment();}</script>

特别推荐：

想在这里投放广告？点击查看详情

• [分享]未来开发趋势!
  四大主题 Web开发2.0 企业开发2.0 系统开发2.0 语言工具2.0
• Unix-Center体验中心
  轻松注册，体验各版本的Unix和类Unix系统 享受多处理器、多核、多线程的高性能计算的乐趣。
• 如何在“工作前”就有“工作经验”！
  蓝点世纪外企高端Java软件工程师培训，采用独创的 项目驱动式培训模式，4个月积累一年以上的经验值！
• 在 OpenMP* 和显式线程方法中选择
  如何在OpenMP*和本地API之间抉择
• 多核编程的几个难题及其应对策略（难题一）
  非常有用的实用资料

<script type="text/javascript">document.write(" ");</script>

上一篇: 列出SQL SERVER 所有表、字段定义，类型，长度，一个值等信息 |  下一篇: 通用存储过程.分页存储过程..返回指定返回条数、指定页数的记录

评论

#     dickc 发表于2007-09-05 11:36:23  IP: 219.153.62.*

SQLSERVER2005下的使用 SELECT 表名 = CASE WHEN A.COLORDER=1 THEN D.NAME ELSE '' END, 表說明 = CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE,'') ELSE '' END, 列序号 = A.COLORDER, 列名 = A.NAME, 标识 = CASE WHEN COLUMNPROPERTY( A.ID,A.NAME,'ISIDENTITY')=1 THEN '√'ELSE '' END, 主键 = CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ=A.ID AND NAME IN ( SELECT NAME FROM SYSINDEXES WHERE INDID IN( SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID))) THEN '√' ELSE '' END, 类型 = B.NAME, 占用位元组数 = A.LENGTH, 长度 = COLUMNPROPERTY(A.ID,A.NAME,'PRECISION'), 小数位数 = ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE'),0), 允许空 = CASE WHEN A.ISNULLABLE=1 THEN '√'ELSE '' END, 预设值 = ISNULL(E.TEXT,''), 說明 = ISNULL(G.[VALUE],'') FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=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 sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id LEFT JOIN sys.extended_properties F ON D.ID=F.major_id AND F.minor_id=0 ORDER BY A.ID,A.COLORDER