资源描述：
《得到sql数据库中所有表字段及字段中文描述》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、得到SQL数据库中所有表字段及字段中文描述 SQL2000写法： ?1234567891011121314151617181920212223242526272829303132333435363738[sql]SELECT (casewhena.colorder=1thend.nameelse''end)N'表名', a.colorderN'字段序号', a.nameN'字段名', (casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end)N'标识', (casewhen(SELECTcount(*) FROM
2、sysobjects WHERE(namein (SELECTname FROMsysindexes WHERE(id=a.id)AND(indidin (SELECTindid FROMsysindexkeys WHERE(id=a.id)AND(colidin (SELECTcolid FROMsyscolumns WHERE(id=a.id)AND(name=a.name)))))))AND (xtype='PK'))>0then'√'else''end)N'主键', b.nameN'类型', a.lengthN'占用字节数', COLUMNPROPERTY(a.id,a.name,
3、'PRECISION')asN'长度', isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小数位数', (casewhena.isnullable=1then'√'else''end)N'允许空', isnull(e.text,'')N'默认值', isnull(g.[value],'')ASN'字段说明' --into##tx FROMsyscolumnsaleftjoinsystypesb ona.xtype=b.xusertype innerjoinsysobjectsd ona.id=d.idandd.xtype='U'
4、andd.name<>'dtproperties' leftjoinsyscommentse ona.cdefault=e.id leftjoinsyspropertiesg ona.id=g.idANDa.colid=g.smallid orderbyobject_name(a.id),a.colorder SQL2005写法：?1234567891011121314151617181920212223242526272829303132333435363738[sql]select table_name= ( casewhent_c.column_id=1
5、 thent_o.name else'' end ), column_id=t_c.column_id, column_name=t_c.name, type=t.name, max_length=t_c.max_length, precision=isnull(t_c.precision,0), scale=isnull(t_c.scale,0), is_identity=casewhent_c.is_identity=1then'√'else''end, is_p
6、rimary= ( casewhenexists ( select1fromsys.indexesi,sys.index_columnsic,sys.objectso whereo.type='PK'ando.name=i.nameandi.index_id=ic.index_id andi.object_id=ic.object_idandic.column_id=t_c.column_id ando.pare
7、nt_object_id=t_c.object_id ) then'√' else'' end ), is_nullable=casewhent_c.is_nullable=1then'√'else''end, default_value=isnull(c.definition,''), description=isnull(
