asp.net 读取 sqlserver 表结构

[w275221545c]

我知道数据库名字 想用asp.net代码 读取该数据库的所有表,以及表下的字段,类型等 请教

[秋水与长天一色]

网站做的很是不错！经典。
至少五万~

[秋水与长天一色]

卖钻石的有money!

[秋水与长天一色]

不好意思跟错贴了！呵呵！

[秋水与长天一色]

select * from syscolumns a

left join sysobjects b on a.id=b.id
where b.name='TableName'

[wzy_love_sly]

create table ta(id int,name varchar(30),date datetime)

create table tb(id int,name varchar(30))


select a.name '表名',b.name '字段名',c.name '类型',b.length '长度' from sysobjects a,syscolumns b,systypes c
where a.id=b.id and a.xtype='u' and b.xtype=c.xtype order by a.name

表名	字段名	类型	长度
ta	id	int	4
ta	name	varchar	30
ta	date	datetime	8
tb	id	int	4
tb	name	varchar	30

[sea_zzh]

刚在学，这个社区真不错！

[boblaw]

代碼不是我寫的，好像是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

[Amy_2008]

select case when c.colid=1 then o.name else '' end 表名, 
  c.colid 顺序, 
  c.name 字段名, 
  t.name 字段类型, 
  columnproperty(c.id,c.name,'PRECISION') 字段长度, 
  isnull(c.Scale,'') 小数位,
  c.length 占用字节,
  case when c.isnullable=1 then '√' else '' end 可为空, 
  case when c.colid in(select ik.colid
    from sysindexes i, Sysindexkeys ik, sysobjects oo
    where i.id=ik.id and i.indid=ik.indid
      and i.name=oo.name and oo.xtype='PK' --主键
      and o.id=i.id 
  ) then '√' else '' end 主键,
  case when c.colid in(select ik.colid
    from sysindexes i, Sysindexkeys ik
    where i.id=ik.id and i.indid=ik.indid
      and o.id=i.id and i.indid=1 --聚类索引
  ) then '√' else '' end 聚类索引,
  case when columnproperty( c.id, c.name,'IsIdentity')=1 then '√' else '' end 自增长,
  isnull(m.text,'') 默认值
from sysobjects o, syscolumns c, systypes t, syscomments m
where o.xtype='U'
  and o.id=c.id 
  and c.xtype=t.xtype
  and c.cdefault*=m.id
order by o.name, c.colid

[一品梅]

14楼正解，呵呵。收藏。

[ReyZhang]

不错,学习

[ReyZhang]

16楼的可以

[wzy_love_sly]

大宋,我2星了

[iuhxq]

收藏下。说不定用得上

[一品梅]

太厉害了，祝贺祝贺，向你学习，煈~~~ :)

[sadan2121]

学习

[lszhengjianghao]

学习!

[rczjp]

MARK

[youngerch]

UP

[wxg22526451]

学习

[w275221545c]

2天没来看 自己当天晚上把他弄出来了 不过谢谢大家参与都给分`~~~