asp得到mssql2000对应库里所有的详细用户表以及字段信息

wangzi6hao

浏览: 213873 次
性别:
来自: sdf

最近访客更多访客>>

ihansel

ldf1991

雪域雄狮lj

caokuang_0

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

ASP SQL Server SQL VBScript JavaScript

<%@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'>&nbsp;</p></td>")
       
        '-----------------------得到字段默认值-----------------
            if rs1("cdefault") = 0 then
                response.write("<td width='69' valign='top'><p>&nbsp;</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>&nbsp;</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>

分享到：