资源描述：
《SQLServer获取SQL所有用户名、数据库名、所有表名、所有字段名.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、1、获取所有用户名:SelectnameFROMSysuserswherestatus='2'andislogin='1'islogin='1':表示帐户islogin='0':表示角色status='2':表示用户帐户status='0':表示糸统帐户2、获取所有数据库名:SelectNameFROMMaster..SysDatabasesorDERBYName3、获取所有表名:SelectNameFROMDatabaseName..SysObjectsWhereXType='U'orDERBYNameXType='U
2、':表示所有用户表;XType='S':表示所有系统表;4、获取所有字段名:SelectNameFROMSysColumnsWhereid=Object_Id('表名'')5、获取数据库所有类型:selectnamefromsystypes6、获取主键字段:SelectnameFROMSysColumnsWhereid=Object_Id('表名')andcolid=(selecttop1keynofromsysindexkeyswhereid=Object_Id('表名'))7、获取表字段的基本信息:程序代码Selec
3、t字段名=rtrim(b.name),主键=CASEWHENh.idISNOTNULLTHEN'PK'ELSE''END,字段类型=type_name(b.xusertype)+CASEWHENb.colstat&1=1THEN'[ID('+CONVERT(varchar,ident_seed(a.name))+','+CONVERT(varchar,ident_incr(a.name))+')]'ELSE''END,长度=b.length,允许空=CASEb.isnullableWHEN0THEN'N'ELSE'Y'E
4、ND,默认值=isnull(e.text,''),字段说明=isnull(c.value,'')FROMsysobjectsa,syscolumnsbLEFTOUTERJOINsyspropertiescONb.id=c.idANDb.colid=c.smallidLEFTOUTERJOINsyscommentseONb.cdefault=e.idLEFTOUTERJOIN(Selectg.id,g.colidFROMsysindexesf,sysindexkeysgWhere(f.id=g.id)AND(f.indid
5、=g.indid)AND(f.indid>0)AND(f.indid<255)AND(f.status&2048)<>0)hON(b.id=h.id)AND(b.colid=h.colid)Where(a.id=b.id)AND(a.id=object_id('要查询的表'))--要查询的表改成你要查询表的名称orDERBYb.colid程序代码Select表名=casewhena.colorder=1thend.nameelse''end,表说明=casewhena.colorder=1thenisnull(f.val
6、ue,'')else''end,字段序号=a.colorder,字段名=a.name,标识=casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end,主键=casewhenexists(Select1FROMsysobjectswherextype='PK'andnamein(SelectnameFROMsysindexesWhereindidin(SelectindidFROMsysindexkeysWhereid=a.idANDcolid=a
7、.colid)))then'√'else''end,类型=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],'')FROMsyscolumnsaleftjoinsys
8、typesbona.xusertype=b.xusertypeinnerjoinsysobjectsdon(a.id=d.id)and(d.xtype='U')and(d.name<>'dtproperties')leftjoinsyscommentseona.cdefault=e.idleftjoinsysprop
