on error resume next
Set primary = con.OpenSchema(adSchemaPrimaryKeys,Array(empty, empty, table))
if primary("COLUMN_NAME")<>"" then
primarykey=primary("COLUMN_NAME")
end if
primary.close
set primary=nothing
%>
<p class="hei">
</p>
<center>
<table width="650" border="1" cellpadding="0" cellspacing="0">
<tr bgcolor="#CCCCCC">
<th width="61">
<div align="center">字段</div>
</th>
<th width="131">
<div align="center">类型</div>
</th>
<th width="105">
<div align="center">设定大小</div>
</th>
<th width="69">
<div align="center">允许空值</div>
</th>
<th width="69">
自动编号
</th>
<th width="81">主键</th>
<th width="120">
<div align="center">执行操作</div>
</th>
</tr>
<%
Strsql="select * from ["&table&"] "
set objRs=con.execute(Strsql)
for i=0 to objRs.fields.count-1
%>
<tr bgcolor="#CCCCCC">
<td height="2" width="61">
<div align="center"><%=objRs(i).name%></div>
</td>
<td height="2" width="131">
<div align="center">
<%
field_type=objRs(i).type
select case field_type
case adEmpty
typ = "Empty"
case adTinyInt
typ = "TinyInt"
case adSmallInt
typ = "SmallInt"
case adInteger
typ = "Integer"
case adBigInt
typ = "BigInt"
case adUnsignedTinyInt
typ = "UnsignedTinyInt"
case adUnsignedSmallInt
typ = "UnsignedSmallInt"
case adUnsignedInt
typ = "UnsignedInt"
case adUnsignedBigInt
typ = "UnsignedBigInt"
case adSingle
typ = "Single"
case adDouble
typ = "Double"
case adCurrency
typ = "Currency"
case adDecimal
typ = "Decimal"
case adNumeric
typ = "Numeric"
case adBoolean
typ = "Boolean"
case adError
typ = "Error"
case adUserDefined
typ = "UserDefined"
case adVariant
typ = "Variant"
case adIDispatch
typ = "IDispatch"
case adIUnknown
typ = "IUnknown"
case adGUID
typ = "GUID"
case adDATE
typ = "DATE"
case adDBDate
typ = "DBDate"
case adDBTime
typ = "DBTime"
case adDBTimeStamp
typ = "DBTimeStamp"
case adBSTR
typ = "BSTR"
case adChar
typ = "Char"
case adVarChar
typ = "VarChar"
case adLongVarChar
typ = "LongVarChar"
case adWChar
typ = "WChar"
case adVarWChar
typ = "VarWChar"
case adLongVarWChar
typ = "LongVarWChar"
case adBinary
typ = "Binary"
case adVarBinary
typ = "VarBinary"
case adLongVarBinary
typ = "LongVarBinary"
case adChapter
typ = "Chapter"
case adPropVariant
typ = "PropVariant"
case else
typ = "Unknown"
end select
response.write typ
%>
</div>
</td>
<td height="2" width="105">
<div align="center"><%=objRs(i).definedsize%></div>
</td>
<td height="2" width="69">
<div align="center">
<%
attrib=objRs(i).attributes
if (attrib and adFldIsNullable)=0 then response.write "No" else response.write "Yes"
%>
</div>
</td>
<td height="2" width="69">
<div align="center">
<input type="checkbox" name="autoincrement" value="checkbox" <%if objRs(i).Properties("ISAUTOINCREMENT") = True then response.write " checked"%>>
</div>
</td>
<td height="2" width="81">
<div align="center">
<input type="checkbox" name="primarykey" value="checkbox" <%if objRs(i).name=primarykey then response.write " checked"%>>
</div>
</td>
</tr>
<%next %>
</table>
</center>
</body>
<%
objRs.close
Con.close
set objRs=nothing
set Con=nothing
%>
Set primary = con.OpenSchema(adSchemaPrimaryKeys, _
Array(empty, empty, table))
if primary("COLUMN_NAME")<>"" then
primarykey=primary("COLUMN_NAME")
end if
primary.close
set primary=nothing
<%if session("admin")=true then%>
<!--#include file="adovbs.inc"-->
<!--#include file="link.asp"-->
<body bgcolor="#CCCCCC" text="#000000">
<%
'接收从上个页面传递过来的表名
%>
<p class="hei">
<%
on error resume next
table=request.QueryString("table")
if table<>"" then
response.write "数据表:"&table
Set primary = con.OpenSchema(adSchemaPrimaryKeys, _
Array(empty, empty, table))
if primary("COLUMN_NAME")<>"" then
primarykey=primary("COLUMN_NAME")
end if
primary.close
set primary=nothing
%>
</p>
<center>
<table width="650" border="1" cellpadding="0" cellspacing="0">
<tr bgcolor="#CCCCCC">
<th width="61">
<div align="center">字段</div>
</th>
<th width="131">
<div align="center">类型</div>
</th>
<th width="105">
<div align="center">设定大小</div>
</th>
<th width="69">
<div align="center">允许空值</div>
</th>
<th width="69">
自动编号
</th>
<th width="81">主键</th>
<th width="120">
<div align="center">执行操作</div>
</th>
</tr>
<%Strsql="select * from ["&table&"] "
set objRs=con.execute(Strsql)
for i=0 to objRs.fields.count-1
%>
<tr bgcolor="#CCCCCC">
<td height="2" width="61">
<div align="center"><%=objRs(i).name%></div>
</td>
<td height="2" width="131">
<div align="center">
<%
field_type=objRs(i).type
select case field_type
case adEmpty
typ = "Empty"
case adTinyInt
typ = "TinyInt"
case adSmallInt
typ = "SmallInt"
case adInteger
typ = "Integer"
case adBigInt
typ = "BigInt"
case adUnsignedTinyInt
typ = "UnsignedTinyInt"
case adUnsignedSmallInt
typ = "UnsignedSmallInt"
case adUnsignedInt
typ = "UnsignedInt"
case adUnsignedBigInt
typ = "UnsignedBigInt"
case adSingle
typ = "Single"
case adDouble
typ = "Double"
case adCurrency
typ = "Currency"
case adDecimal
typ = "Decimal"
case adNumeric
typ = "Numeric"
case adBoolean
typ = "Boolean"
case adError
typ = "Error"
case adUserDefined
typ = "UserDefined"
case adVariant
typ = "Variant"
case adIDispatch
typ = "IDispatch"
case adIUnknown
typ = "IUnknown"
case adGUID
typ = "GUID"
case adDATE
typ = "DATE"
case adDBDate
typ = "DBDate"
case adDBTime
typ = "DBTime"
case adDBTimeStamp
typ = "DBTimeStamp"
case adBSTR
typ = "BSTR"
case adChar
typ = "Char"
case adVarChar
typ = "VarChar"
case adLongVarChar
typ = "LongVarChar"
case adWChar
typ = "WChar"
case adVarWChar
typ = "VarWChar"
case adLongVarWChar
typ = "LongVarWChar"
case adBinary
typ = "Binary"
case adVarBinary
typ = "VarBinary"
case adLongVarBinary
typ = "LongVarBinary"
case adChapter
typ = "Chapter"
case adPropVariant
typ = "PropVariant"
case else
typ = "Unknown"
end select
response.write typ%>
</div>
</td>
<td height="2" width="105">
<div align="center"><%=objRs(i).definedsize%></div>
</td>
<td height="2" width="69">
<div align="center">
<%
attrib=objRs(i).attributes
if (attrib and adFldIsNullable)=0 then
response.write "No"
else
response.write "Yes"
end if
%>
</div>
</td>
<td height="2" width="69">
<div align="center">
<%if objRs(i).Properties("ISAUTOINCREMENT") = True then%>
<input type="checkbox" name="autoincrement" value="checkbox" checked>
<%else%>
<input type="checkbox" name="autoincrement" value="checkbox">
<%end if%>
</div>
</td>
<td height="2" width="81">
<div align="center">
<%if objRs(i).name=primarykey then%>
<input type="checkbox" name="primarykey" value="checkbox" checked>
<%else%>
<input type="checkbox" name="primarykey" value="checkbox">
<%end if%>
</div>
</td>
</tr>
<%next %>
</table>
</center>
</body>
<%
Con.close
objRs.close
set Con=nothing
set objRs=nothing
%>
set cat = Server.CreateObject("ADOX.Catalog")
Set cat.ActiveConnection = conn
Set tblOD = cat.Tables("YourTableName")
%>
Column Information:
<table border="1" cellspacing="0" cellpadding="0">
<thead>
<th>Column Name</th>
<th>Type</th>
<th>NumericScale</th>
<th>Precision</th>
<th>Nullable</th>
</thead>
<%
For Each colLoop In tblOD.Columns
%>
<tr>
<td><%=colLoop.Name%></td>
<td><%=colLoop.Type%></td>
<td><%=colLoop.NumericScale%></td>
<td><%=colLoop.Precision%></td>
<td><%=(colLoop.Attributes AND 2) <> 0%></td>
</tr>
<%
Next
%>
</table>
Index information:
<table border="1" cellspacing="0" cellpadding="0">
<thead>
<th>Index Name</th>
<th>Is Primary?</th>
<th>is Unique?</th>
<th>Columns</th>
</thead>
<%
For Each idxLoop In tblOD.Indexes
%>
<tr>
<td><%=idxLoop.Name%></td>
<td><%=idxLoop.PrimaryKey%></td>
<td><%=idxLoop.Unique%></td>
<%
s = ""
For Each colLoop In idxLoop.Columns
s = s & "," & colLoop.Name
Next
if len(s) > 0 then
s = mid(s,2)
end if
%>
<td><%=s%></td>
</tr>
<%
Next
conn.Close
Set cat = Nothing
Set conn = Nothing
%>
如何得到属性不太清楚,但用SQL语句创建表的时候应该可以创建主键、是否非空、默认值、索引。下面是一段vb中的相关代码,稍加改动后应该可以用在asp中:
Dim conn1 As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim SQLStr As String, SQLCreate As String, SQLAlter As String
SQLStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\abc.mdb;"
Set conn1 = New ADODB.Connection
conn1.ConnectionString = SQLStr
conn1.Open
SQLCreate = "create table temptable" & _
"(H_ID int not null constraint H_ID_PK primary key," & _
"english char(30) not null default ENG," & _
"chinese char(50) not null default CHN)"
SQLAlter = "create index index_temptable " & _
"on temptable(english)"
Set Rs = conn1.Execute(SQLCreate, 1, adCmdText)
Set Rs = Nothing
Set Rs = conn1.Execute(SQLAlter, 1, adCmdText)
Set Rs = Nothing