环球网校是美国纳斯达克上市企业欢聚时代(NASDAQ:YY)旗下品牌 | 住房和城乡建设部 建筑人才培训合作单位
您现在的位置在: > 计算机类 > 计算机等级考试 > 考试辅导 >

如何导出SQL所有用户表的字段信息

2010-06-28 来源:互联网 作者:第一考试网

如何导出SQL所有用户表的字段信息 #

       问题:怎样才能导出SQL所有用户表的字段信息?
  解答:请使用下面的Sql语句:
  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 #

责编:admin 返回顶部  打印

关于我们联系我们友情链接网站声明网站地图广告服务帮助中心