如何获取一个数据库中的所有
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
的名称
?如何获取一个数据库中的所有表的名称、一个表中所有字段的名称,
select name from sysobjects where xtype='U' 1、如何用SQL获取一个数据库中的所有表的名称; select name from syscolumns where id=object_id(N'name') 2、如何用SQL获取一个表中所有字段的名称; good2speed(Goodspeed) 于 2005-5-19 12:20:35 ~
good2speed(Goodspeed) 于 2005-5-19 11:56:43 1、如何用SQL获取一个数据库中的所有表的名称;
SELECT * FROM sysobjects
1、如何用SQL获取一个数据库中的所有表的名称; WHERE xtype='u'
SELECT * FROM sysobjects
WHERE xtype='u' 2、如何用SQL获取一个表中所有字段的名称;
SELECT c.name,o.name FROM syscolumns AS c 2、如何用SQL获取一个表中所有字段的名称; INNER JOIN sysobjects AS o
SELECT c.name,o.name FROM syscolumns AS c ON c.id = o.id
INNER JOIN sysobjects AS o xluzhong(Ralph) 于 2005-5-19 12:48:03 ON c.id = o.id
pbsql(风云) 于 2005-5-19 11:56:59 --查询表结构信息
--邹老大的
select name from sysobjects where xtype='U' SELECT
select name from syscolumns where id=object_id(N'表明') 表名=case when a.colorder=1 then d.name else '' end, paoluo(一天到晚游泳的鱼) 于 2005-5-19 11:59:42 表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
Select Name from sysobjects where Type='U' 标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '?'else '' end, Select Name from syscolumns where ID=OBJECT_ID(N'TableName') Order By ColID 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and mengzulin(Julian) 于 2005-5-19 12:02:14 name in (
SELECT name FROM sysindexes WHERE indid in( SELECT dbo.sysobjects.name AS [table], dbo.syscolumns.name AS columns SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid FROM dbo.sysobjects INNER JOIN ))) then '?' else '' end,
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id 类型=b.name,
WHERE (dbo.sysobjects.xtype = 'u') 占用字节数=a.length,
dazhu2(边城浪子【倚天不出,谁与争锋】) 于 2005-5-19 12:12:11 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 学习 允许空=case when a.isnullable=1 then '?'else '' end, huangdun(惇惇) 于 2005-5-19 12:18:27 默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'') a.name N'字段名',
FROM syscolumns a (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '?'else ' end) N'标识', left join systypes b on a.xusertype=b(case when (SELECT .xusertype count(*) inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' FROM sysobjects
left join syscomments e on a.cdefault=e.id WHERE (name in left join sysproperties g on a.id=g.id and a.colid=g.smallid name (SELECT
left join sysproperties f on d.id=f.id and f.smallid=0 FROM sysindexes
--where d.name='要查询的表' --如果只查询指定表,加上此条件 WHERE (id = a.id) AND (indid in order by a.id,a.colorder (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) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', ?先创建一个视图 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', Create view fielddesc (case when a.isnullable=1 then '?'else ' end) N'允许空', as isnull(e.text,') N'默认值',
select o.name as table_name,c.name as field_name,t.name as type,c.length as isnull(g.[value],') AS N'字段说明' length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp --into ##tx
from syscolumns c FROM syscolumns a left join systypes b join systypes t on c.xtype = t.xusertype on a.xtype=b.xusertype
join sysobjects o on o.id=c.id inner join sysobjects d left join sysproperties p on p.smallid=c.colid and p.id=o.id on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' where o.xtype='U' left join syscomments e 查询时: on a.cdefault=e.id
Select * from fielddesc where table_name = '你的表名' left join sysproperties g 还有个更强的语句,是邹建写的,也写出来吧 on a.id=g.id AND a.colid = g.smallid SELECT order by object_name(a.id),a.colorder (case when a.colorder=1 then d.name else ' end) N'表名',
a.colorder N'字段序号',