资源描述：
《获取mssql表结构中字段的备注、主键等信息》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、获取MSSQL表结构中字段的备注、主键等信息 ?123456789101112131415161718192021222324252627282930313233343536373839401、MSSQL2000SELECT 表名 =casewhena.colorder=1thend.nameelse''end, 表说明 =casewhena.colorder=1thenisnull(f.value,'')else''end, 字段序号 =a.colorder, 字段名 =a.name, 标识 =casewhenCOLUMNPR
2、OPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end, 主键 =casewhenexists(SELECT1FROMsysobjectswherextype='PK'andparent_obj=a.idandnamein( SELECTnameFROMsysindexesWHEREindidin( SELECTindidFROMsysindexkeysWHEREid=a.idANDcolid=a.colid)))then'√'else''end,
3、 类型 =b.name, 占用字节数=a.length, 长度 =COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小数位数 =isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允许空 =casewhena.isnullable=1then'√'else''end, 默认值 =isnull(e.text,''), 字段说明 =isnull(g.[value],'')FROM syscolumnsaleftjoin systypes
4、bon a.xusertype=b.xusertypeinnerjoin sysobjectsdon a.id=d.id andd.xtype='U'and d.name<>'dtproperties'leftjoin syscommentseon a.cdefault=e.idleftjoin syspropertiesgon a.id=g.idanda.colid=g.smallid leftjoin syspropertiesfon4142434445464748495051525354555657585960616263646566676
5、8697071727374757677787980818283 d.id=f.idandf.smallid=0where d.name='FI_dept' --如果只查询指定表,加上此条件orderby a.id,a.colorder 2、MSSQL2005 usetest--數據庫go--2005實現字段屬性統計(2000里的系統表sysproperties描述表、字段不存在，2005里用sys.extended_properties視圖替代)select [表名]=c.Name, [表说明]=isnull(f.[value],''), [列名]=
6、a.Name, [列序號]=a.Column_id, [標識]=casewhenis_identity=1then'√'else''end, [主鍵]=casewhenexists(select1fromsys.objectswhereparent_object_id=a.object_idandtype=N'PK'andnamein (selectNamefromsys.indexeswhereindex_idin (selectindidfromsysindexkeyswhereandcolid=
7、a.column_id))) then'√'else''end, [類型]=b.Name, [字節數]=casewhena.[max_length]=-1andb.Name!='xml'then'max/2G' whenb.Name='xml'then'2^31-1字節/2G' elsertrim(a.[
