select o.name tablename, c.name fieldname, t.name fieldtype, columnproperty(c.id,c.name,'PRECISION') fieldlen, c.Scale,c.length, c.colid fieldorder, c.isnullable, 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 ) then1else0end isPrimaryKey, 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--聚类索引 ) then1else0end isClusterKey, columnproperty( c.id, c.name,'IsIdentity') IsIdentity, isnull(m.text,'') defaultvalue from sysobjects o, syscolumns c, systypes t, syscomments m where o.xtype='U' and o.id=c.id and c.xtype=t.xtype and c.cdefault*=m.id orderby o.name, c.colid
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='U' and o.id=c.id and c.xtype=t.xtype and c.cdefault*=m.id orderby o.name, c.colid