请SQL语句高手帮忙解决问题?????在先等候

wensyCool 2005-03-15 03:19:01
要的到数据库中都有那些表,SQL怎么写(在SQL查询分析器中写)
...全文
88 5 点赞 打赏 收藏 举报
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wensyCool 2005-03-15
多谢
  • 打赏
  • 举报
回复
chiwei 2005-03-15
是从网上看到的,就摘下来了:


--列出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





  • 打赏
  • 举报
回复
jinjazz 2005-03-15
得到所有的数据表(用户数据表):
select * from sysobjects where xtype = 'u'
得到当前数据表的所有字段:
select * from syscolumns where id = object_id('数据表名')
  • 打赏
  • 举报
回复
子陌红尘 2005-03-15
select name from sysobjects where type = N'U'
  • 打赏
  • 举报
回复
paoluo 2005-03-15
Select * from SysObjects Where Xtype='U' or Xtype='S' Order By Name
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2005-03-15 03:19
社区公告
暂无公告