SQL SERVER

查看所有表名： select    name    from    sysobjects    where    type='U'

查询表的所有字段名： Select name from syscolumns Where ID=OBJECT_ID('表名')

select * from information_schema.tables select * from information_schema.views select * from information_schema.columns

ACCESS

查看所有表名： select    name    from    MSysObjects    where    type=1    and    flags=0

MSysObjects是系统对象，默认情况是隐藏的。通过工具、选项、视图、显示、系统对象可以使之显示出来。  

--获取表字段名称，属性 SELECT      表名       = case when a.colorder=1 then d.name else '' end,     表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,     字段序号   = a.colorder,     字段名     = a.name,     标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,     主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (                      SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,     类型       = b.name,     占用字节数 = a.length,     长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),     小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),     允许空     = case when a.isnullable=1 then '√'else '' end,     默认值     = isnull(e.text,''),     字段说明   = isnull(g.[value],'') FROM      syscolumns a left join      systypes b  on      a.xusertype=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  left join      sysproperties f  on      d.id=f.id and f.smallid=0 where      d.name='要查询的表'    --如果只查询指定表,加上此条件 order by      a.id,a.colorder

--获取库中所有表字段的定义（包括名字，属性等） --1. SqlServer数据库字典--表结构.sql

/** 表a为syscolumns 表b为systypes 表d为sysobjects 表e为syscomments 表g为sysproperties */ 

SELECT TOP 100 PERCENT --a.id,  CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,  CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,  a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,  a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,  CASE WHEN EXISTS  (SELECT 1  FROM dbo.sysindexes si INNER JOIN  dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN  dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN  dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK'  WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,  b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')  AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,  CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')  AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,  CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间  FROM dbo.syscolumns a LEFT OUTER JOIN  dbo.systypes b ON a.xtype = b.xusertype INNER JOIN  dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND  d.status >= 0 LEFT OUTER JOIN  dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN  dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN  dbo.sysproperties f ON d.id = f.id AND f.smallid = 0  ORDER BY d.name, a.colorder