<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!--使用说明,将文件名改为database,并将conn文件导入.更改相对的数据库就可以查询用户表信息.-->
<!--#include file="../inc/conn.asp"--><%'连接数据库,就不要我弄了吧%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>数据库表</title>
</head>
<body>
<%
dim rs,sql,i,intPage,page,pre,last,filepath
set rs = server.CreateObject("adodb.recordset")
sql="select * from dbo.sysobjects where type = 'u'and [name]<>'dtproperties' order by crdate"
rs.PageSize = 8 //（Yoko:这里设定每页显示的记录数
rs.CursorLocation = 3
rs.Open sql,conn,0,2,1 //（Yoko:'这里执行你查询SQL并获得结果记录集
pre = true
last = true
page = trim(Request.QueryString("page"))
if not isNumeric(page) then
intpage = 1
pre = false
end if
if len(page) = 0 then
intpage = 1
pre = false
else
if cint(page) =< 1 then
intpage = 1
pre = false
else
if cint(page) >= rs.PageCount then
intpage = rs.PageCount
last = false
else
intpage = cint(page)
end if
end if
end if
if not rs.eof then
rs.AbsolutePage = intpage
end if
%>
<!--循环开始-->
<%
k = 1
for i=1 to rs.PageSize '循环显示所有表名
if rs.EOF or rs.BOF then exit for
response.write("表名:" & k & "<font color=red>" & rs("name") & "</font> ")
response.write("创建时间:<font color=red>" & rs("crdate") & "</font><br>")
response.write("<table border='1' cellspacing='0' cellpadding='0' width='684'><tr>")
response.write("<td width='47' valign='top'><strong>编号</strong> </td>")
response.write("<td width='100' valign='top'><p align='center'><strong>字段名 </strong></p></td>")
response.write("<td width='63' valign='top'><p align='center'><strong>类型 </strong></p></td>")
response.write("<td width='59' valign='top'><p align='center'><strong>字段长 </strong></p></td>")
response.write("<td width='59' valign='top'><p align='center'><strong>允许空 </strong></p></td>")
response.write("<td width='47' valign='top'><p align='center'><strong>是否为主键 </strong></p></td>")
response.write("<td width='59' valign='top'><p align='center'><strong>小数位 </strong></p></td>")
response.write("<td width='69' valign='top'><p align='center'><strong>默认值 </strong></p></td>")
response.write("<td width='49' valign='top'><p align='center'><strong>备注 </strong></p></td>")
response.write("<td width='131' valign='top'><p align='center'><strong>说明 </strong></p></td></tr>")
'-----rs 根据表名的id查询所有的字段名--------
dim rs1,sql1
sql1 = "select * from syscolumns where id = "& rs("id")& " order by colid"
set rs1 = server.CreateObject("adodb.recordSet")
rs1.open sql1,conn,1,1
j = 1
do while not(rs1.eof or rs1.bof)
'-------rs1循环显示对应表名的字段名---------
response.write("<tr><td width='47' valign='top'><p align='center'>" & j & "</p></td>")
response.write("<td width='100' valign='top'><p>"& rs1("name") &"</p></td>")
'------------rs2得到相对字段的类型-----------------
dim rs2,sql2
sql2 = "select * from systypes where xusertype = "&rs1("xusertype")
set rs2 = server.createObject("adodb.recordset")
rs2.open sql2,conn,1,1
response.write("<td width='63' valign='top'><p>"&rs2("name")&"</p></td>")
rs2.close()
set rs2 = nothing
'-------------rs2得到相对字段类型结束---------------
response.write("<td width='59' valign='top'><p>"&rs1("length")&"</p></td>")'得到字段长度
'--------------------得到字段是否充许为空-------------------
if rs1("isnullable") = 0 then
response.write("<td width='59' valign='top'><p align='center'>n</p></td>")
else
response.write("<td width='59' valign='top'><p align='center'>y</p></td>")
end if
'--------------------得到字段是否充许为空结束-----------------
'----------------得到字段是否为主键信息---------------------
if rs1("colstat") = 1 then
response.write(" <td width='47' valign='top'><p align='center'>y</p></td>")
else
response.write(" <td width='47' valign='top'><p align='center'>n</p></td>")
end if
'----------------得到字段是否为主键信息结束---------------------
response.write("<td width='59' valign='top'><p align='center'> </p></td>")
'-----------------------得到字段默认值-----------------
if rs1("cdefault") = 0 then
response.write("<td width='69' valign='top'><p> </p></td>")
else
dim rs3,sql3
sql3 = "select * from syscomments where id = "&rs1("cdefault")
set rs3 = server.CreateObject("adodb.recordset")
rs3.open sql3,conn,1,1
if not (rs3.eof or rs3.bof) then
response.write("<td width='69' valign='top'><p>"&left(rs3("text"),20)&"</p></td>")
end if
rs3.close
set rs3 = nothing
end if
'------------------得到字段默认值结束----------------------
response.write("<td width='49' valign='top'><p> </p></td>")
'------------------得到字段描述值开始----------------------
dim rs4,sql4
sql4 = "select * from sysproperties where smallid = "&rs1("colid")
set rs4 = server.CreateObject("adodb.recordset")
rs4.open sql4,conn,1,1
'response.write("<td width='131' valign='top'><p></p>"&rs4("value")&"</td></tr>")
rs4.close
set rs4 = nothing
'------------------得到字段描述值结束----------------------
rs1.movenext()
j = j + 1
loop
'------------rs1结束---------------
rs1.close()
set rs1 = nothing
response.write("</table><br><br>")
k = k + 1
rs.movenext
next
'--------------------rs结束------------
%>
<table width="684" border="0" cellpadding="0" cellspacing="0" borderColorLight=#808080 borderColorDark=#ffffff>
<tr>
<%if rs.pagecount > 0 then%>
<td width="13%" align="left">当前页<%=intpage%>/<%=rs.PageCount%></td>
<%else%>
<td width="16%" align="left">当前页0/0</td>
<%end if%>
<td width="71%" align="right"> <a href="database.asp?page=1">首页</a>|
<%if pre then%>
<a href="database.asp?page=<%=intpage -1%>">上页</a>| <%end if%>
<%if last then%>
<a href="database.asp?page=<%=intpage +1%>">下页</a> |<%end if%>
<a href="database.asp?page=<%=rs.PageCount%>">尾页</a>|转到第
<select name="sel_page" onChange="javascript:location=this.options[this.selectedIndex].value;">
<%
for i = 1 to rs.PageCount
if i = intpage then%>
<option value="database.asp?page=<%=i%>" selected><%=i%></option>
<%else%>
<option value="database.asp?page=<%=i%>"><%=i%></option>
<%
end if
next
%>
</select>页</font>
</td>
</tr>
</table>
<%
rs.close()
set rs = nothing
conn.close()
set conn = nothing
%>
select * from sysobjects where xtype = 'u'
<!------这里表sysobjects是所有表名称的表,xtype='u'字段表示是此表是用户新建. crdate表示用户新建时间------->
select * from sysindexes
select * from sysindexkeys
select * from syscomments where id = 645577338
<!--表syscomments主要是放置字段默认值.其id值是和syscolumns.cdefault字段关联,当syscolumns.cdefault不0时,是有默认值的.syscomments.text是字段的默认值-->
select * from systypes where xusertype = 175
<!--表systypes字段的类型,其中xusertype为syscolumns.xusertype字段名,表示为字段的类型号.此字段里的name就是类型.-->
select * from syscolumns where id = 581577110 order by colid
<!----这里表syscolumns是表示所有表的字段名,id和表sysobjects.id相同,表示不同表所生成的字段.colid表示字段新建的顺序.length是字段长度.isnullable是表示是否的标记,0表示不可以为空,1表示可以为空----->
</body>
</html>asp得到mssql2000对应库里所有的详细用户表以及字段信息
最新推荐文章于 2021-09-11 16:16:23 发布
206
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
