SQL提取数据库表名及字段名等信息代码示例
本文向大家介绍了使用SQL语句提取数据库所有表的表名、字段名的实例代码，在SQLserver中进行了测试，具体内容如下：
--查询所有用户表所有字段的特征 SELECTD.NameasTableName,A.colorderASColOrder,A.nameASName, COLUMNPROPERTY(A.ID,A.Name,'IsIdentity')ASIsIdentity, CASEWHENEXISTS (SELECT1 FROMdbo.sysobjects WHEREXtype='PK'ANDNameIN (SELECTName FROMsysindexes WHEREindidIN (SELECTindid FROMsysindexkeys WHEREID=A.IDANDcolid=A.colid))) THEN1ELSE0ENDAS主键, B.nameAS类型, A.lengthAS[长度], A.xprecAS[精度], A.xscaleAS[小数], CASEWHENA.isnullable=1THEN1ELSE0ENDAS[可为空], ISNULL(E.text,'')AS[默认值], ISNULL(G.[value],'')AS[说明] FROMdbo.syscolumnsALEFTOUTERJOIN dbo.systypesBONA.xtype=B.xusertypeINNERJOIN dbo.sysobjectsDONA.id=D.idANDD.xtype='U'AND D.name<>'dtproperties'LEFTOUTERJOIN dbo.syscommentsEONA.cdefault=E.id LEFTOUTERJOINdbo.syspropertiesGONA.id=G.idANDA.colid=G.smallid --WHERED.Name='tablename'--如果找指定表,把注释去掉 ORDERBY1,2
------在SQLserver2005中测试
--查询所有用户表所有字段的特征 SELECT (casewhena.colorder=1thend.nameelse''end)as表名,--如果表名相同就返回空 a.colorderas字段序号, a.nameas字段名, (casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end)as标识, (casewhen(SELECTcount(*)FROMsysobjects--查询主键 WHERE(namein (SELECTnameFROMsysindexes WHERE(id=a.id)AND(indidin (SELECTindidFROMsysindexkeys WHERE(id=a.id)AND(colidin (SELECTcolidFROMsyscolumns WHERE(id=a.id)AND(name=a.name)) ))))) AND(xtype='PK'))>0then'√'else''end)as主键,--查询主键END b.nameas类型, a.lengthas占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION')as长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)as小数位数, (casewhena.isnullable=1then'√'else''end)as允许空, isnull(e.text,'')as默认值, isnull(g.[value],'')AS字段说明 FROMsyscolumnsaleftjoinsystypesb ona.xtype=b.xusertype innerjoinsysobjectsd ona.id=d.idandd.xtype='U'andd.name<>'dtproperties' leftjoinsyscommentse ona.cdefault=e.id leftjoinsys.extended_propertiesg ona.id=g.major_idANDa.colid=g.minor_id --whered.name='PE_U_ValliMessage'--所要查询的表 orderbya.id,a.colorder
------提取某个表的字段名
SELECT (casewhena.colorder=1thend.nameelse''end)N'表名', a.colorderN'字段序号', a.nameN'字段名', isnull(g.[value],'')ASN'字段说明', (casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else'' end)N'标识', (casewhen(SELECTcount(*) FROMsysobjects 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,'PRECISION')asN'长度', isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小数位数', (casewhena.isnullable=1then'√'else''end)N'允许空', isnull(e.text,'')N'默认值' FROMsyscolumnsa leftjoinsystypesb ona.xtype=b.xusertype innerjoinsysobjectsd ona.id=d.idandd.xtype='U'andd.name<>'dtproperties' leftjoinsyscommentse ona.cdefault=e.id leftjoinsys.extended_propertiesg ona.id=g.major_idANDa.colid=g.minor_id --where条件输入表名 whered.name='ConsumeBillBody' orderbyobject_name(a.id),a.colorder
总结
以上就是本文关于SQL提取数据库表名及字段名等信息代码示例的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站：SQL注入原理与解决方法代码示例、MySQL删除有外键约束的表数据方法介绍、几个比较重要的MySQL变量等，有什么问题可以随时留言，小编会及时回复大家的。感谢朋友们对本站的支持！