use asp to know the table's struction of sql servler

最新推荐文章于 2025-08-22 11:49:22 发布

原创最新推荐文章于 2025-08-22 11:49:22 发布 · 677 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #table #asp #sqlserver #menu #border

asp 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一段使用ASP代码来读取SQL Server数据库中表的结构信息的方法，包括表名及各字段的名称、类型及其属性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用asp读取sqlserver的数据表,字段结构,抄至某网上代码,经过验证,可以用

<%
set conn = server.createobject("adodb.connection")
conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=211.111.12.1;UID=jevons;PWD=jevons;DATABASE=jevons"

sql1="select * from dbo.sysobjects where type not in('s','d','k')"
sql1="select * from dbo.sysobjects where type ='u'"
set rscc = server.createobject("adodb.recordset")
set rs = server.createobject("adodb.recordset")
rscc.open sql1,conn,1,2

do while not rscc.eof

   sql ="select * from "&rscc("name")

   rs.open sql,conn,1,2
response.write "<hr>表名"&rscc("name")&"<br>"
if "1"="1" then
for i=0 to rs.fields.count-1

response.write "字段名:"&rs.fields(i).name &"<br>"
response.write "类型:"
if rs.fields(i).type="3" then
    response.write "int"
    if rs.fields(i).attributes="16" then response.write " 自动编号字段"
    if rs.fields(i).attributes="120" then response.write " 允许空"
    if rs.fields(i).attributes="24" then response.write " 不允许空"
elseif rs.fields(i).type="129" then
    response.write "char"
    response.write "大小"&rs.fields(i).definedsize
    if rs.fields(i).attributes="120" then response.write " 允许空"
    if rs.fields(i).attributes="24" then response.write " 不允许空"
elseif rs.fields(i).type="201" then
      response.write "text"
      if rs.fields(i).attributes="234" then response.write " 允许空"
      if rs.fields(i).attributes="138" then response.write " 不允许空"
elseif rs.fields(i).type="202" then
      response.write "nvarchar"
      response.write "大小"&rs.fields(i).definedsize
      if rs.fields(i).attributes="8" then response.write " !     不允许空"
      if rs.fields(i).attributes="104" then response.write "!     允许空"
elseif rs.fields(i).type="131" then
      response.write "numeric"
      if rs.fields(i).attributes="120" then response.write " 允许空"
      if rs.fields(i).attributes="24" then response.write " 不允许空"
elseif rs.fields(i).type="2" then
      response.write "smallint"
      if rs.fields(i).attributes="120" then response.write "!     允许空"
      if rs.fields(i).attributes="24" then response.write " !     不允许空"
elseif rs.fields(i).type="11" then
     response.write "bit"
     if rs.fields(i).attributes="120" then response.write "!     允许空"
     if rs.fields(i).attributes="24" then response.write " !     不允许空"
elseif rs.fields(i).type="135" then
     response.write "smalldatetime"
     if rs.fields(i).attributes="120" then response.write "!     允许空"
     if rs.fields(i).attributes="24" then response.write " !     不允许空"
elseif rs.fields(i).type="203" then
     response.write "ntext"
     if rs.fields(i).attributes="234" then response.write "!     允许空"
     if rs.fields(i).attributes="138" then response.write " !     不允许空"
elseif rs.fields(i).type="6" then
     response.write "money"
     if rs.fields(i).attributes="120" then response.write "!     允许空"
     if rs.fields(i).attributes="24" then response.write " !     不允许空"
elseif rs.fields(i).type="130" then
     response.write "nchar"
     if rs.fields(i).attributes="120" then response.write "!     允许空"
     if rs.fields(i).attributes="24" then response.write " !     不允许空"
elseif rs.fields(i).type="4" then
     response.write "real"
    if rs.fields(i).attributes="120" then response.write "!     允许空"
    if rs.fields(i).attributes="24" then response.write " !     不允许空"
elseif rs.fields(i).type="5" then
    response.write "flat"
    if rs.fields(i).attributes="120" then response.write "!     允许空"
    if rs.fields(i).attributes="24" then response.write " !     不允许空"
elseif rs.fields(i).type="128" then
    response.write "binary"
    if rs.fields(i).attributes="120" then response.write "!     允许空"
    if rs.fields(i).attributes="24" then response.write " !     不允许空"
else
    response.write "未知"&rs.fields(i).type
end if

response.write " 属性:"&rs.fields(i).attributes
response.write "-------------<br>"

next
end if
rs.close

'-------------------------------------
rscc.movenext
loop

'-------------------------------------test begin------------------------------------
'表名 left_menu
'字段名: id
'类型:int 属性:32784-------------
'字段名: dpar_name
'类型:未知200 属性:104-------------
'字段名: dpar_par_id
'类型:int 允许空属性:120-------------
'字段名: dpar_puth
'类型:未知200 属性:104-------------
'字段名: dpar_dpar_id
'类型:未知200 属性:104-------------
'字段名: dpar_limit
'类型:未知200 属性:104-------------

sql1="select * from left_menu"
set rstest = server.createobject("adodb.recordset")
rstest.open sql1,conn,1,2

response.write "<h1>left_menu</h1><table border=1>"

response.write "<tr><td>id</td><td>dpar_name</td><td>dpar_par_id</td><td>dpar_puth</td><td>dpar_dpar_id</td><td>dpar_limit</td></tr>"

do while not rstest.eof
response.write "<tr><td>"&rstest("id")&"</td><td>"&rstest("dpar_name")&"</td><td>"&rstest("dpar_par_id")&"</td><td>"&rstest("dpar_puth")&"</td><td>"&rstest("dpar_dpar_id")&"</td><td>"&rstest("dpar_limit")&"</td></tr>"
rstest.MoveNext

loop
rstest.close
set rstest=nothing
response.write "</table>"
'插入数据国际经销商 15 into_jingxiaoshang.asp 0
'conn.execute("UPDATE cus_list SET opt_us
' sql="insert into left_menu(dpar_name,dpar_par_id,dpar_puth,dpar_limit) values('国际经销商','15','j_into_jingxiaoshang.asp','0')"
'conn.execute(sql)
'
' 表名 yucun_jingxiaoshang
' 字段名:id ' 类型:int 属性:32784-------------
' 字段名:sheng' 类型:int 允许空属性:120-------------
' 字段名:shi' 类型:int 允许空属性:120-------------
' 字段名:jingxiaoshang_name' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_tele' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_mobi' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_fax' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_url' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_qq' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_msn' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_mail' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_job' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_post' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_addr' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_note' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_user' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_date' 类型:smalldatetime! 允许空属性:120-------------
' 字段名:jingxiaoshang_men' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_good' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_price' 类型:未知200 属性:104-------------
' 字段名:jingxiaoshang_way' 类型:未知200 属性:104-------------
'alter table test add col_n int default('1')

'sql="alter table yucun_jingxiaoshang add inport_export int default('0')"
'conn.execute(sql)
'sql="update yucun_jingxiaoshang set inport_export =0"
'conn.execute(sql)

' sql1="select inport_export from yucun_jingxiaoshang"
'set rsinout = server.createobject("adodb.recordset")
'rsinout.open sql1,conn,1,2
'
' response.write "<h1>inport_export</h1><table border=1>"
'
'
' do while not rsinout.eof
' response.write "<tr><td>"&rsinout("inport_export")&"</td> </tr>"
' rsinout.MoveNext
'
' loop
' rsinout.close
' set rsinout=nothing
' response.write "</table>"
'-------------------------------------test end------------------------------------------
set rs=nothing
set rscc=nothing

set conn=nothing

%>
finished