SQL 语句获取表字段名称，属性

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