declare@tablenamevarchar(50) declare@vbchar(1) set@tablename='tempmain' --set @vb='U' --表 set@vb='V'--视图 selectcasewhen c.colid=1then o.name else''end 表名, c.colid 顺序, c.name 字段名, t.name 字段类型, columnproperty(c.id,c.name,'PRECISION') 字段长度, isnull(c.Scale,'') 小数位, c.length 占用字节, casewhen c.isnullable=1then'√'else''end 可为空, casewhen c.colid in(select ik.colid from sysindexes i, Sysindexkeys ik, sysobjects oo where i.id=ik.id and i.indid=ik.indid and i.name=oo.name and oo.xtype='PK'--主键 and o.id=i.id ) then'√'else''end 主键, casewhen c.colid in(select ik.colid from sysindexes i, Sysindexkeys ik where i.id=ik.id and i.indid=ik.indid and o.id=i.id and i.indid=1--聚类索引 ) then'√'else''end 聚类索引, casewhencolumnproperty( c.id, c.name,'IsIdentity')=1then'√'else''end 自增长, isnull(m.text,'') 默认值 from sysobjects o, syscolumns c, systypes t, syscomments m where o.xtype=@vb and o.name=@tablename and o.id=c.id and c.xtype=t.xtype and c.cdefault*=m.id orderby o.name, c.colid
获得单表或视图的结构。
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 = si.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 AND g.name ='MS_Description'LEFTOUTERJOIN dbo.sysproperties f ON d.id = f.id AND f.smallid =0AND f.name ='MS_Description' ORDERBY d.name, a.colorder