资源描述：
《数据字典制作SQL语句.doc》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、--sqlserver2000SELECT表名=casewhena.colorder=1thend.nameelse''end,表说明=casewhena.colorder=1thenisnull(f.value,'')else''end,字段序号=a.colorder,字段名=a.name,标识=casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end,主键=casewhenexists(SELECT1FROMsysobjectswherextype='PK'a
2、ndparent_obj=a.idandnamein(SELECTnameFROMsysindexesWHEREindidin(SELECTindidFROMsysindexkeysWHEREid=a.idANDcolid=a.colid)))then'√'else''end,类型=b.name,占用字节数=a.length,长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),允许空=casewhena.
3、isnullable=1then'√'else''end,默认值=isnull(e.text,''),字段说明=isnull(g.[value],'')FROMsyscolumnsaleftjoinsystypesbona.xusertype=b.xusertypeinnerjoinsysobjectsdona.id=d.idandd.xtype='U'andd.name<>'dtproperties'leftjoinsyscommentseona.cdefault=e.idleftjoinsyspropertiesgona.id=g.i
4、danda.colid=g.smallidleftjoinsyspropertiesfond.id=f.idandf.smallid=0whered.name='要查询的表'--如果只查询指定表,加上此条件orderbya.id,a.colorder/*表名表说明字段序号字段名标识主键类型占用字节数长度小数位数允许空默认值字段说明--------------------------------------------------------------------------------------------authors1au_id√
5、id111102au_lnamevarchar404003au_fnamevarchar202004phonechar12120('UNKNOWN')5addressvarchar40400√6cityvarchar20200√7statechar220√8zipchar550√9contractbit110（所影响的行数为9行）*/--以下是SQLSERVER2005的代码--sqlserver2005--1.表结构信息查询--=======================================================
6、=================--表结构信息查询--邹建2005.08(引用请保留此信息)--========================================================================SELECTTableName=CASEWHENC.column_id=1THENO.nameELSEN''END,TableDesc=ISNULL(CASEWHENC.column_id=1THENPTB.[value]END,N''),Column_id=C.column_id,ColumnNam
7、e=C.name,PrimaryKey=ISNULL(IDX.PrimaryKey,N''),[IDENTITY]=CASEWHENC.is_identity=1THENN'√'ELSEN''END,Computed=CASEWHENC.is_computed=1THENN'√'ELSEN''END,Type=T.name,Length=C.max_length,Precision=C.precision,Scale=C.scale,NullAble=CASEWHENC.is_nullable=1THENN'√'ELSEN''END,[D
8、efault]=ISNULL(D.definition,N''),ColumnDesc=ISNULL(PFD.[value],N''),IndexName=ISNULL(IDX.IndexNa
