sql server2008怎么实现查询某个数据库中所有的表名
答案:3 悬赏:50
解决时间 2021-02-02 06:13
- 提问者网友：斯文败类
- 2021-02-01 15:06
sql server2008怎么实现查询某个数据库中所有的表名
最佳答案
- 二级知识专家网友：闲懒诗人
- 2021-02-01 15:54
-- 名名称和字段名
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 sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id
order by a.id,a.colorder
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 sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id
order by a.id,a.colorder
全部回答
- 1楼网友：木子香沫兮
- 2021-02-01 17:36
select name
from sys.sysobjects
where [type]='u'是要这样？
- 2楼网友：陪我到地狱流浪
- 2021-02-01 17:16
你是要写代码生成器么? --这是查表的数量 select [name] from sysobjects where xtype='u'and [name]<>'dtproperties' order by [name] --得到数据库中所有用户视图 select [name] from sysobjects where xtype='v' and [name]<>'syssegments' and [name]<>'sysconstraints' order by [name] --获得指定表中所有的列 select c.name as columnname, t.name as typename from syscolumns c, systypes t, sysobjects o where c.xtype = t.xusertype and c.id = o.id and o.name = 'book' order by c.colorder
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
大家都在看
推荐资讯