如何获取一个数据库中的所有表的名称

1、?如何获取一个数据库中的所有表的名称、一个表中所有字段的名称?1、如何用SQL获取一个数据库中的所有表的名称 ；2、如何用SQL获取一个表中所有字段的名称；!good2speed（Goodspeed）于 2005-5-19 11:56:431、如何用SQL获取一个数据库中的所有表的名称 ；SELECT * FROM sysobjectsWHERE xtype=u2、如何用SQL获取一个表中所有字段的名称；SELECT , FROM syscolumns AS cINNER JOIN sysobjects AS oON c.id = o.idpbsql（风云）于 2005

2、-5-19 11:56:59select name from sysobjects where xtype=Uselect name from syscolumns where id=object_id（N表明）paoluo（天到晚游泳的鱼）于 2005-5-19 11:59:42Select Name from sysobjects where Type=USelect Name from syscolumns where ID=OBJECT_ID（NTableName） Order By CollD mengzulin（Julian）于 2005-5-19 12:02:14SELECT d

3、 AS table, AS columnsFROM dbo.sysobjects INNER JOINdbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.idWHERE （dbo.sysobjects.xtype = u）dazhu2（边城浪子【倚天不出，谁与争锋】 ）于2005-5-19 12:12:11学习huangdun（惇惇）于 2005-5-19 12:18:27select name from sysobjects where xtype=Uselect

4、 name from syscolumns where id=object_id(Nname)good2speed(Goodspeed)于 2005-5-19 12:20:351、如何用SQL获取一个数据库中的所有表的名称；SELECT * FROM sysobjectsWHERE xtype=u2、如何用SQL获取一个表中所有字段的名称；SELECT , FROM syscolumns AS cINNER JOIN sysobjects AS oON c.id = o.idxluzhong(Ralph)于 2005-5-19 12:48:03-查询表结构信息-邹老大的

5、SELECT表名=case when a.colorder=1 then else end,表说明=case when a.colorder=1 then isnull(f.value,) else end,字段序号=a.colorder,字段名=a.n ame,标识=case when COLUMNPROPERTY( a.id,,lsldentity)=1 then Velse end,主键=casewhen exists(SELECT 1 FROM sysobjects where xtype=PK and parent_obj=a.id and name in

6、(SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid)then V else end,类型=,占用字节数=a.length,长度=COLUMNPROPERTY(a.id,, PRECISION，),小数位数=isnull(COLUMNPROPERTY(a.id,,Scale),0),允许空=case when a.isnullable=1 then /else end,默认值=isnull(e.t

7、ext,),字段说明=isnull(g.value,)FROM syscolumns aleft join systypes b on a.xusertype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype=U and odtpropertiesleft join syscomments e on a.cdefault=e.idleft join sysproperties g on a.id=g.id and a.colid=g.smallidleft join sysproperties f on d.id

8、=f.id and f.smallid=O-where -要查询的表-如果只查询指定表，加上此条件order by a.id,a.colorder?先创建一个视图Create view fielddescasselect as table_name, as field_name, as type,cength aslength,c.isnullable as isnullable,convert(varchar(30),p.value) as despfrom syscolumns cjoin systypes t on c.xtype = t

9、.xusertypejoin sysobjects o on o.id=c.idleft join sysproperties p on p.smallid=c.colid and p.id=o.idwhere o.xtype=U查询时：Select * from fielddesc where table_name =你的表名还有个更强的语句，是邹建写的，也写出来吧SELECT(case when a.colorder=1 then else end) N表名a.colorder N字段序号a. name N，字段名(case when COLUMNPROPERTY( a.id

10、,,lsldentity)=1 then Velse end) N标识(case when (SELECT count(*)FROM sysobjectsWHERE (name in(SELECT nameFROM sysindexesWHERE (id = a.id) AND (indid in(SELECT indidFROM sysindexkeysWHERE (id = a.id) AND (colid in(SELECT colidFROM syscolumnsWHERE (id = a.id) AND (name = ) AND(xtype =卩K)0 th

11、en V else end) N主键b. name lT类型a.length N，占用字节数COLUMNPROPERTY(a.id,,PRECISION) as N长度isnull(COLUMNPROPERTY(a.id,,Scale),0) as N小数位数(case when a.isnullable=1 then Velse end) N允许空isnull(e.text,) N默认值isnull(g.value；) AS N字段说明-into #txFROM syscolumns a left join systypes bon a.xtype=b.xusertypeinner join sysobjects don a.id=d.id a