首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 计算机考试 > 等级考试 > 考试试题 >

怎样才能导出SQL所有用户表的字段信息

2009-03-17 
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

 

3COME考试频道为您精心整理,希望对您有所帮助,更多信息在http://www.reader8.com/exam/

热点排行