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
    表名       =casewhen a.colorder=1then d.name else''end,
    表说明     =casewhen a.colorder=1thenisnull(f.value,'') else''end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       =casewhenCOLUMNPROPERTY( a.id,a.name,'IsIdentity')=1then'√'else''end,
    主键       =casewhenexists(SELECT1FROM 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),
    允许空     =casewhen a.isnullable=1then'√'else''end,
    默认值     =isnull(e.text,''),
    字段说明   =isnull(g.[value],'')
FROM
    syscolumns a
leftjoin
    systypes b
on
    a.xusertype=b.xusertype
innerjoin
    sysobjects d
on
    a.id=d.id and d.xtype='U'and d.name<>'dtproperties'
leftjoin
    syscomments e
on
    a.cdefault=e.id
leftjoin
    sysproperties g
on
    a.id=g.id and a.colid=g.smallid
leftjoin
    sysproperties f
on
    d.id=f.id and f.smallid=0
where
    d.name='要查询的表'    --如果只查询指定表,加上此条件
orderby
    a.id,a.colorder

 

 

 

 

 

 

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

 

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

SELECTTOP100PERCENT--a.id,

CASEWHEN a.colorder =1THEN d.name ELSE''ENDAS 表名,

CASEWHEN a.colorder =1THENisnull(f.value, '') ELSE''ENDAS 表说明,

a.colorder AS 字段序号, a.name AS 字段名, CASEWHENCOLUMNPROPERTY(a.id,

a.name, 'IsIdentity') =1THEN'√'ELSE''ENDAS 标识,

CASEWHENEXISTS

(SELECT1

FROM dbo.sysindexes si INNERJOIN

dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNERJOIN

dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNERJOIN

dbo.sysobjects so ON so.name = so.name AND so.xtype ='PK'

WHERE sc.id = a.id AND sc.colid = a.colid) THEN'√'ELSE''ENDAS 主键,

b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')

AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,

CASEWHEN a.isnullable =1THEN'√'ELSE''ENDAS 允许空, ISNULL(e.text, '')

AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,

CASEWHEN a.colorder =1THEN d.refdate ELSENULLENDAS 更改时间

FROM dbo.syscolumns a LEFTOUTERJOIN

dbo.systypes b ON a.xtype = b.xusertype INNERJOIN

dbo.sysobjects d ON a.id = d.id AND d.xtype ='U'AND

d.status >=0LEFTOUTERJOIN

dbo.syscomments e ON a.cdefault = e.id LEFTOUTERJOIN

dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFTOUTERJOIN

dbo.sysproperties f ON d.id = f.id AND f.smallid =0

ORDERBY d.name, a.colorder