语法
set connection=server.createobject(“adodb.connection“)
Set recordset = connection.OpenSchema (QueryType, Criteria, SchemaID)
返回值
返回包含模式信息的 Recordset 对象。Recordset 将以只读、静态游标打开。
参数
QueryType 所要运行的模式查询类型，可以为下列任意常量。
Criteria 可选。每个 QueryType 选项的查询限制条件数组，
参数枚举（QueryType ）
'---- SchemaEnum Values ----
Const adSchemaProviderSpecific = -1
Const adSchemaAsserts = 0
Const adSchemaCatalogs = 1
Const adSchemaCharacterSets = 2
Const adSchemaCollations = 3
Const adSchemaColumns = 4
Const adSchemaCheckConstraints = 5
Const adSchemaConstraintColumnUsage = 6
Const adSchemaConstraintTableUsage = 7
Const adSchemaKeyColumnUsage = 8
Const adSchemaReferentialContraints = 9
Const adSchemaTableConstraints = 10
Const adSchemaColumnsDomainUsage = 11
Const adSchemaIndexes = 12
Const adSchemaColumnPrivileges = 13
Const adSchemaTablePrivileges = 14
Const adSchemaUsagePrivileges = 15
Const adSchemaProcedures = 16
Const adSchemaSchemata = 17
Const adSchemaSQLLanguages = 18
Const adSchemaStatistics = 19
Const adSchemaTables = 20
Const adSchemaTranslations = 21
Const adSchemaProviderTypes = 22
Const adSchemaViews = 23
Const adSchemaViewColumnUsage = 24
Const adSchemaViewTableUsage = 25
Const adSchemaProcedureParameters = 26
Const adSchemaForeignKeys = 27
Const adSchemaPrimaryKeys = 28
Const adSchemaProcedureColumns = 29
利用openschema可以获得所有表的主键，当然这只是它的很多功能中的一个。
connstr = "Provider=MSDAORA.1;User ID=liujincai;password=ljc1001;Data Source=hp1" set mm=server.CreateObject("adodb.connection") mm.Open connstr Const adSchemaPrimaryKeys = 28 set rs=mm.OpenSchema(adSchemaPrimaryKeys) Response.Write "<table>" Response.Write "<TR>" for i=0 to rs.fields.count-1 Response.Write "<td>" & rs(i).name & "</td>" next Response.Write "</tr> " do while not rs.eof Response.Write "<tr>" for i=0 to rs.fields.count-1 Response.Write "<td>" & rs(i) & "</td>" next Response.Write "</tr> " rs.movenext loop Response.write “</table>“ '下面的语句获得表'TB_HOUSE_MAIN'的主键所在的字段 rs.Filter="TABLE_NAME='TB_HOUSE_MAIN'" if not(rs.eof and rs.bof) then Response.Write rs("COLUMN_NAME")
获得任意一个存储过程的参数列表，这个对于想做完全松耦合系统的哥们一定有用的。这个程序获得名字为'PRO_HOUSE_ADD_INFO'的存储过程的参数列表
connstr = "Provider=MSDAORA.1;User ID=liujincai;password=ljc1001;Data Source=hp1" set mm=server.CreateObject("adodb.connection") mm.Open connstr Const adSchemaProcedureParameters = 26 set rs=mm.OpenSchema(adSchemaProcedureParameters) rs.Filter="PROCEDURE_NAME='PRO_HOUSE_ADD_INFO'" Response.Write "<TABLE cellSpacing=0 border=1>" Response.Write "<TBODY><TR bgColor=#dddddd>" for i=2 to rs.fields.count-1 Response.Write "<TD>" & rs(i).name & "</TD>" next Response.Write "</TR>" do while not rs.eof Response.Write "<TR bgColor=#e0d0c0>" for i=2 to rs.fields.count-1 Response.Write "<TD>" & rs(i) & "</TD>" next Response.Write "</tr>" rs.movenext loop Response.Write "</table>"
经过几个小时的实践探索，终于结束了对Adodb.connection的openschema方法的研究。现在总结一下：
1，Set recordset = connection.OpenSchema (QueryType, Criteria, SchemaID)
里边一共有三个参数，但是一般就用第一个，后两个得确切功能，我也没有搞懂，这里也不做说明，有兴趣的自己查资料。
2，这个的实现就是通过连接对象的openschema方法返回一个记录集。这样就可以遍历记录集来显示或者搜集感性趣的信息。
3，利用它你还可以获得关于表，视图，列，索引，外键等信息。
4，现在不能肯定的是，是否有些方式的模式查询受用户权限的限制。也就是如果没有足够的权限，是不能做某些类别的模式查询的（有待验证）
5，这里有一些关于OpenSchema的资料连接，供查阅：
http://www.lyu.edu.cn/home/yss/ado/mdmthopenschema.htm
http://code365.com/html/asp/20040227/index/2004227113621.html
在ASP中列出数据库中的表名和字段名的程序
Set rstSchema = oConn.OpenSchema(adSchemaColumns)
这是关键之处
<% dsn = "DSN=DSNName" Const adSchemaTables = 20 adSchemaColumns = 4 Set oConn = Server.CreateObject("ADODB.Connection") oConn.open dsn Set rstSchema = oConn.OpenSchema(adSchemaColumns) response.write "<table>" response.write "<tr><td>Table name</td><td>field name</td><td>field type</td><td>is nullable</td><td>field size</td></tr>" tablename="" Do Until rstSchema.EOF response.write "<tr><td>" if rstSchema("Table_name") <> tablename then response.write rstSchema("Table_name") tablename = rstSchema("Table_name") else response.write " " end if response.write "</td><td>"& rstschema("column_Name") & "</td><td>" select case rstschema("data_type") case "130" if rstschema("CHARACTER_MAXIMUM_LENGTH") = 1073741823 then response.write "Memo" else response.write "Text" end if case 135 response.write "Date/Time" case 3 response.write "Long Integer" case 11 response.write "Yes/No" case 131 response.write "Currency" case else response.write rstschema("data_type") end select response.write "</td><td>" & rstschema("is_nullable") & "</td><td>" if rstschema("CHARACTER_MAXIMUM_LENGTH") <> 1073741823 then response.write rstschema("CHARACTER_MAXIMUM_LENGTH") else response.write " " end if response.write "</td></tr>" rstSchema.MoveNext Loop response.write "</table>" %> |
