Tutorial
Grammar
Set Connection=server.createobject ("Adodb.connection")
Set Recordset = connection. OpenSchema (QueryType, Criteria, SchemaID)
return value
Returns the Recordset object that contains the schema information. The Recordset opens with a read-only, static cursor.
Parameters
QueryType the mode query type you want to run, you can be any of the following constants.
The Criteria are optional. An array of query restriction criteria for each querytype option,
Parameter Enumeration (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
Using OpenSchema, you can get the primary key for all tables, but this is just one of many of its features.
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 as 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>"
' The following statement obtains the field of the primary key of table ' Tb_house_main '
Rs. filter= "Table_name= ' Tb_house_main '"
If not (rs.eof and RS.BOF) then Response.Write rs ("column_name")
Get a list of parameters for any stored procedure, which is useful for buddies who want to do a completely loose-coupled system. This program gets the list of parameters for the stored procedure named ' 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 "
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 as 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>"
After several hours of practical exploration, finally ended the study of the OpenSchema method of Adodb.connection. Now summarize:
1,set Recordset = connection. OpenSchema (QueryType, Criteria, SchemaID)
Inside a total of three parameters, but generally use the first, the latter two have the exact function, I also did not understand, here also do not explain, interested in their own information.
2, the implementation of this is to return a recordset through the OpenSchema method of the Connection object. This allows you to traverse the recordset to display or collect interesting information.
3, using it you can also get information about tables, views, columns, indexes, foreign keys, and so on.
4, it is now not certain that there are some ways in which the mode query is restricted by user permissions. That is, if you do not have sufficient permissions, you cannot do certain types of schema queries (pending verification).
5, here are some information about the OpenSchema connection, for reference:
Http://www.lyu.edu.cn/home/yss/ado/mdmthopenschema.htm
Http://code365.com/html/asp/20040227/index/2004227113621.html
program that lists table names and field names in the database in ASP
Set Rstschema = Oconn.openschema (adSchemaColumns)
That's the point.
<%
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>"
%>
