在程序中,如何知道SQl的某个库中都有那些表存在呀.谢谢

同上

解决方案 »

  1.   

    将SQL SERVER中所有表的列信息显示出来:http://dev.csdn.net/article/69/69679.shtm
      

  2.   

    select * from sysobjects where xType='u'如果是SQL。执行这个语句咯
      

  3.   

    <html>
    <head>
    <base target="_self">
    <script language="vbscript">
    class getsqlinfo
      dim the_adox
       Private Sub Class_Initialize    
          set the_adox=createobject("ADOX.Catalog")
       end sub   public sub sqlserver_getactiveconnection(dsource,dbasename,uid,pwd)
           the_adox.ActiveConnection="Provider=sqloledb;Data Source=" & dsource & ";Initial Catalog=" & dbasename & ";User ID=" & uid & ";PWD=" & pwd & ";"
          
       end sub   public sub access_getactiveconnection(theaccessfile)
          the_adox.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theaccessfile
       end sub     public function tablename
          dim a()
      dim i : i=1
      for each objtab in the_adox.tables
      if objtab.type="TABLE" then
         redim Preserve a(i)
     a(i-1)=objtab.name
    ' alert(objtab.type)
     i=i+1
      end if
      next   tablename=a
       end function    public function getfieldinfo(thetabname)
       dim a()
       set thetableadox=the_adox.tables.item(thetabname)
       for i=0 to thetableadox.columns.count-1
              set ttt=createobject("scripting.dictionary")
           redim preserve a(i+1)
         ' alert(thetableadox.columns.Item(i).type)
          ttt.add "name",thetableadox.columns.Item(i).name
          ttt.add "type",thetableadox.columns.Item(i).type
          set a(i)=ttt
          set ttt=nothing
       next
      ' alert(isArray(a))
       getfieldinfo=a
       end function   Private Sub Class_Terminate
            set the_adox=nothing
       End Subend class
    </script>
    <script language="vbscript">
    function chooseaccessfolder()
    set getdll=createobject("getfolderd.folderd")
    thefoldername=getdll.getfoldername
    set getaccessurl=document.getElementById("showaccessurl")
    getaccessurl.innertext=thefoldername
    end function
    </script><script language="vbscript">
    dim save_infofunction init()
    set save_info=createobject("scripting.dictionary")
    end functionfunction copydbsestr()
    set the_dbasestr=document.getElementById("dbasestr")
    set the_dbasetext=the_dbasestr.createTextRange()
    the_dbasetext.execCommand("copy")
    end functionfunction showaccesstab
    set save_info=nothing
    set save_info=createobject("scripting.dictionary")
    set theshowaccesssql=document.getElementById("showaccesssql")
    theshowaccesssql.style.display="none"
    dim tabname
    dim str : str=""
    set theaccess_choose=document.getElementById("showaccessurl")
    set theshow_accesstab=document.getElementById("showaccesstab")
    theaccessfile=theaccess_choose.value
    if len(theaccessfile)<>0 then
    set accessclass=new getsqlinfo
    On Error Resume Next
    call accessclass.access_getactiveconnection(theaccessfile)
    if Err.Number=0 then
    str="连接串是：" & "<textarea id='dbasestr' rows='2' cols='50'>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theaccessfile & "' </textarea><a href='vbscript:copydbsestr()'>copy</a><br />"
    tabname=accessclass.tablename
    str=str & "请选择你要的表" & "<br />"
    for i=0 to ubound(tabname)-1
      str=str & "<input type='radio' name='tabnames' value='" & tabname(i) & "' />" & tabname(i) & "<br />"
    next
    str=str & "<a href='vbscript:showsql'>确定</a>"
    theshow_accesstab.innerhtml=str
    save_info.add "accessfile",accessclass
    theshow_accesstab.style.display="block"
    else
     alert("请选择一个access文件")
    end if
    else
    alert("请选择一个access文件")
    end if
    end functionfunction copyaccessinsert()
    set the_dbasestr=document.getElementById("accessinsertstrid")
    set the_dbasetext=the_dbasestr.createTextRange()
    the_dbasetext.execCommand("copy")
    end functionfunction copyaccessupdate()
    set the_dbasestr=document.getElementById("accessupdatestrid")
    set the_dbasetext=the_dbasestr.createTextRange()
    the_dbasetext.execCommand("copy")
    end functionfunction showsql
    set thetabnames=document.getElementsByName("tabnames")
    set theshowaccesssql=document.getElementById("showaccesssql")
    for i=0 to thetabnames.length-1
    if thetabnames(i).checked=true then
    thetabname=thetabnames(i).value
    end if
    next
    if len(thetabname)<>0 then
    str="insert语句：" & "<textarea id='accessinsertstrid' rows='2' cols='50'>" & accessinsertsqlstr(thetabname) & "</textarea>"
    str=str & "<a href='vbscript:copyaccessinsert()'>copy</a><br />"
    'str=str & "<a herf='vbscript:copyaccessinsert()'>copy</a><br />"
    str=str & "update语句：" & "<textarea id='accessupdatestrid' rows='2' cols='50'>" & accessupdatesqlstr(thetabname) & "</textarea><a href='vbscript:copyaccessupdate()'>copy</a>"
    theshowaccesssql.innerhtml=str
    theshowaccesssql.style.display="block"
    else
    alert("请选择一个表")
    end if
    end functionfunction judge_charnum(thefldname,thefldtype)
    if thefldtype>100 then
    str="'" & """" & " & " & thefldname & " & " & """" &  "'"
    else
    str="""" & " & " & thefldname & " & " & """"
    end if 
    judge_charnum=str
    end functionfunction judge_charnumfinal(thefldname,thefldtype)
    if thefldtype>100 then
    str="'" & """" & " & " & thefldname & " & " & """" &  "'" & """"
    else
    str="""" & " & " & thefldname
    end if 
    judge_charnumfinal=str
    end function
      

  4.   

    function accessupdatesqlstr(thetabname)
    dim fieldnames
    fieldnames=getfieldname(thetabname)
    str="""" & "update " & thetablename & "set "
    for i=0 to ubound(fieldnames)-1
     if i<>ubound(fieldnames)-1 then
       str=str & fieldnames(i)("name") & "=" & judge_charnum(fieldnames(i)("name"),fieldnames(i)("type"))  
    else
      str=str & fieldnames(i)("name") & "=" & judge_charnumfinal(fieldnames(i)("name"),fieldnames(i)("type"))
     end if
    nextaccessupdatesqlstr=str
    end functionfunction accessinsertsqlstr(thetabname)
    dim fieldnames
    fieldnames=getfieldname(thetabname)
    str="""" & "insert into " & thetablename & "("
    for i=0 to ubound(fieldnames)-1
     if i<>ubound(fieldnames)-1 then
      str=str & fieldnames(i)("name") & ","
     else
       str=str & fieldnames(i)("name")
      end if
    next
    str=str & ")" & " values("
    for i=0 to ubound(fieldnames)-1
    if i<>ubound(fieldnames)-1 then
      str=str & judge_charnum(fieldnames(i)("name"),fieldnames(i)("type")) & ","
     else
       str=str & judge_charnum(fieldnames(i)("name"),fieldnames(i)("type"))
      end if
    next
    str=str & ")" & """"
    accessinsertsqlstr=str
    end functionfunction getfieldname(thetabname)
    dim a
    'alert(thetabname)
    a=save_info("accessfile").getfieldinfo(thetabname)
    'alert(isArray(a))
    getfieldname=a
    end functionfunction showaccessdiv()
    set access_div=document.getElementById("writeaccesschoose")
    access_div.style.display="block"
    set sqlserver_div=document.getElementById("writesqlserverchoose")
    sqlserver_div.style.display="none"
    end function function filechange()
    set theshowaccesssql=document.getElementById("showaccesssql")
    theshowaccesssql.style.display="none"
    set access_div=document.getElementById("writeaccesschoose")
    access_div.style.display="block"
    set sqlserver_div=document.getElementById("writesqlserverchoose")
    sqlserver_div.style.display="none"
    end functionfunction showsqlserverdiv
    set theshow_accesstab=document.getElementById("showaccesstab")
    theshow_accesstab.style.display="none"
    set access_div=document.getElementById("writeaccesschoose")
    access_div.style.display="none"
    set sqlserver_div=document.getElementById("writesqlserverchoose")
    sqlserver_div.style.display="block"
    set theshowaccesssql=document.getElementById("showaccesssql")
    theshowaccesssql.style.display="none"
    end functionfunction showsqlservertab
    set save_info=nothing
    set save_info=createobject("scripting.dictionary")
    set theshowaccesssql=document.getElementById("showsqlserversql")
    theshowaccesssql.style.display="none"
    dim tabname
    dim str : str=""
    set sqlserver_dbsource=document.getElementById("dbsource")
    set sqlserveruser=document.getElementById("user")
    set sqlserver_pwd=document.getElementById("pwd")
    set sqlserver_dbasename=document.getElementById("dbasename")
    thedbsource=sqlserver_dbsource.value
    theuser=sqlserveruser.value
    thepwd=sqlserver_pwd.value
    thedbasename=sqlserver_dbasename.value
    set sqlserverclass=new getsqlinfo
    On Error Resume Next
    call sqlserverclass.sqlserver_getactiveconnection(thedbsource,thedbasename,theuser,thepwd)
    if Err.Number=0 then
    str="连接串是：" & "<textarea id='dbasestr' rows='2' cols='50'>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theaccessfile & "' </textarea><a href='vbscript:copydbsestr()'>copy</a><br />"
    tabname=accessclass.tablename
    str=str & "请选择你要的表" & "<br />"
    for i=0 to ubound(tabname)-1
      str=str & "<input type='radio' name='tabnames' value='" & tabname(i) & "' />" & tabname(i) & "<br />"
    next
    str=str & "<a href='vbscript:showsql'>确定</a>"
    theshow_accesstab.innerhtml=str
    save_info.add "accessfile",accessclass
    theshow_accesstab.style.display="block"
    else
     alert("请选择一个access文件")
    end if
    end function
    </script></head>
    <body onload="init()">
    <div id="start">
       选择access数据库：<input type='radio' name='choose_accorsql' value='access' onpropertychange="showaccessdiv()" /><br />
       选择sql数据库：<input type='radio' name='choose_accorsql' value='sql' onpropertychange="showsqlserverdiv()" /><br />
    </div>
    <div id="writeaccesschoose" style="display:none;">
    选择access文件：<br />
    <input type="file" id="showaccessurl" onpropertychange="filechange()" />
    <a href="vbscript:showaccesstab">确定</a>
    </div>
    <div id="showaccesstab">
    </div>
    <div id="showaccesssql">
    </div>
    <div id="writesqlserverchoose" style="display:none;">
    请输入sql server服务器名或IP：<input type='text' id='dbsource' /><br />
    请输入用户名：<input type='text' id='user' /><br />
    请输入密码：<input type='password' id='pwd' /><br />
    输入数据库的名字：<input type='text' id='dbasename' /><a href='vbscript:showsqlservertab'>确定</a><br />
    </div>
    <div id="showsqlservertab">
    </div>
    <div id="showsqlserversql">
    </div>
    </body>
    </html>
      

  5.   

    这个就是vb啊，晕，你看不懂vb代码的？放到vb就是vb代码了
      

  6.   

    晕。 If Rs.State <> adStateClosed Then Rs.Close
      Rs.Open "select * from sysobjects where xType='u'", Conn, adOpenStatic, adLockOptimistic
      
      
      Me.List1.Clear
      
      Do While Not Rs.EOF
        Me.List1.AddItem Rs(0)
        Rs.MoveNext
      Loop
      这样你不会吗？ado 访问你不会吗？？？？？晕死