quinta-feira, 18 de abril de 2002

Visualização da estrutura das tabelas do BD

Postado por Rubens Farias às 09:06

<% Option Explicit %>
<!--#include file="adovbs.inc"-->
<%
Dim oConexao, oTabela, oADOXDB, i
Dim oConn, oTableRs, oColumnRs
Dim sTabela, spConexao


'## Função que retorna o tipo da informção que compõe o campo ##
Function FieldType(sfpType,sfpLength,sfpPrecision)
Dim sfRetorno


   Select Case sfpType
       Case "2"
          sfRetorno = "smallint"
       Case "3"
          sfRetorno = "int"
       Case "4"
          sfRetorno = "real"
       Case "11"
          sfRetorno = "bit"
       Case "17"
          sfRetorno = "tinyint"
       Case "128"
          sfRetorno = "image"
       Case "129","130"
          if sfpLength = "2147483647" then
             sfRetorno = "text"
          else
             sfRetorno = "char/varchar"
          end if
       Case "5","131"
          if sfpPrecision > 0 then
            sfRetorno = "decimal"
          else
            sfRetorno = "numeric"
          end if
       Case "7","135"
          sfRetorno = "datetime"
       Case Else
          sfRetorno = "(indefinido)"
   End Select
   FieldType = sfRetorno


End Function


   '## Pega a tabela selecionada na URL ##
   sTabela = request.querystring("tabela")


   '## String de Conexao ##
   'spConexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PATH+BANCO_DADOS.MDB"
   'spConexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&server.mappath("BANCO_DADOS.MDB")


   '## Conexao com o BD ##
   Set oADOXDB = Server.CreateObject("ADOX.Catalog")
   oADOXDB.ActiveConnection = spConexao
%>
<html>
<head>
<title>:::: Estrutura da(s) Tabela(s) ::::  por Myers</title>
<style type="text/css">
<!--
.tblTituloX {  border-top: double 1px #000000; border-left: double 1px #000000; border-right: double 1px #000000; font-family: verdana; font-weight: bold; font-size: 11px}
.tblTitulo0 {  border-top: solid 1px #000000; border-bottom: solid 1px #000000; border-left: solid 1px #000000; border-right: solid 1px #000000; font-family: verdana; font-weight: bold; font-size: 11px}
.tblTitulo1 {  border-top: solid 1px #000000; border-bottom: solid 1px #000000; border-right: solid 1px #000000; font-family: verdana; font-weight: bold; font-size: 11px}
.tblCelula0 {  border-bottom: solid 1px #E7E7E7; border-left: solid 1px #E7E7E7; border-right: solid 1px #E7E7E7; font-family: verdana; font-size: 11px}
.tblCelula1 {  border-bottom: solid 1px #E7E7E7; border-right: solid 1px #E7E7E7; font-family: verdana; font-size: 11px}
.lsvTblNor{color: #000000; background-color: #E7E7E7; font :  Verdana, Geneva, Arial, Helvetica, sans-serif; font-weight : bold; font-size: 8px}
.lsvTblSel{color: #000000; background-color: #CCCCCC; font :  Verdana, Geneva, Arial, Helvetica, sans-serif; font-size: 8px}
.lsvTblOut{ border : 1px solid; border-color: #000000;   font-size: 11px;}
-->
</style>
</head>


<body bgcolor="#FFFFFF">
<form method="post" action="<%= request.ServerVariables("SCRIPT_NAME") %>" name="frmListaTabelas">
  <blockquote>
  <select name="cmbTabelas" onChange="window.location='<%= request.ServerVariables("SCRIPT_NAME") %>?tabela='+this.value" class="lsvTblOut">
    <option value="0" class="lsvTblNor">:::: Selecione a tabela :::: </option>
    <% if sTabela = "showalltables" then %>
    <option value="showalltables" class="lsvTblSel" selected>- Todas -</option>
    <% else %>
    <option value="showalltables" class="lsvTblNor">- Todas -</option>
    <% end if %>
    <%
    '## Preenche o select com as tabelas do Banco de Dados ##
    for each oTabela in oADOXDB.Tables
       if oTabela.Type = "TABLE" then
          if LCase(oTabela.Name) = LCase(sTabela) then
             response.write "    <option value="""&oTabela.Name&""" class=""lsvTblSel"" selected>"&_
                            oTabela.Name&"</option>"&vbCRLF
          else
             response.write "    <option value="""&oTabela.Name&""" class=""lsvTblNor"">"&_
                            oTabela.Name&"</option>"&vbCRLF
          end if
       end if
    next
    %>
  </select>
  </blockquote>
  <br>
  <%
   if sTabela <> "" or not IsNull(sTabela) then
      if Lcase(sTabela) = Lcase("showalltables") then


     '## Exibicao TODAS as Tabelas ##
      Set oConn = Server.CreateObject("ADODB.Connection")
      oConn.ConnectionString = spConexao
      oConn.Open


      '## Retorna as tabelas do BD ##
      Set oTableRS = oConn.OpenSchema(adSchemaTables, _
                     Array(Empty, Empty, Empty, "TABLE"))


      do while not oTableRS.EOF
  %>
  <table border="0" cellspacing="0" cellpadding="2" width="90%" align="center">
    <tr bgcolor="#CCCCCC">
      <td colspan="7" align="center" class="tblTituloX"><%=oTableRS("Table_Name").Value%></td>
    </tr>
    <tr bgcolor="#E7E7E7">
      <td class="tblTitulo0" nowrap>CAMPO</td>
      <td class="tblTitulo1" nowrap>TIPO</td>
      <td class="tblTitulo1" nowrap>TAMANHO</td>
      <td class="tblTitulo1" nowrap>PRECIS&Atilde;O</td>
      <td class="tblTitulo1" nowrap>ESCALA</td>
      <td class="tblTitulo1" nowrap>PERMITIR NULO</td>
      <td class="tblTitulo1" nowrap>VALOR PADR&Atilde;O</td>
    </tr>
    <%
         '## Retorna as colunas de cada tabela (oTableRS("Table_Name").Value) ##
         Set oColumnRs = oConn.OpenSchema(adSchemaColumns, _
            Array(Empty, Empty, oTableRS("Table_Name").Value))
         do while not oColumnRs.EOF
    %>
    <tr>
      <td class="tblCelula0" nowrap><%=oColumnRs("Column_Name")%>&nbsp;</td>
      <td class="tblCelula1" nowrap><%response.write FieldType(oColumnRs("Data_Type"),_
           oColumnRs("Character_Maximum_Length"),oColumnRs("Numeric_Precision"))&_
           "("&oColumnRs("Data_Type")&")"%>&nbsp;</td>
      <td class="tblCelula1" nowrap><%=oColumnRs("Character_Maximum_Length")%>&nbsp;</td>
      <td class="tblCelula1" nowrap><%=oColumnRs("Numeric_Precision")%>&nbsp;</td>
      <td class="tblCelula1" nowrap><%=oColumnRs("Numeric_Scale")%>&nbsp;</td>
      <td class="tblCelula1" align="center" nowrap>
      <% if oColumnRs("Is_Nullable") then %>
        <input type="checkbox" name="ckbNulo" checked>
      <% else %>
        <input type="checkbox" name="ckbNulo">
      <% end if %>
      </td>
      <td class="tblCelula1" nowrap><%=oColumnRs("Column_Default")%>&nbsp;</td>
       </tr>
     <%
         oColumnRs.MoveNext
         loop


     %>
      </table><p>
     <%
      Set oColumnRs = Nothing
      oTableRS.MoveNext
      loop



      oTableRS.Close
      Set oTableRS = nothing


      oConn.Close
      Set oConn = Nothing


  else
      '## Exibe tabelas separadas ##


      '## Abre a conexao
      Set oConn = Server.CreateObject("ADODB.Connection")
      oConn.ConnectionString = spConexao
      oConn.Open


      '## Retorna as Colunas da tabela selecionada (sTabela) ##
      Set oColumnRs = oConn.OpenSchema(adSchemaColumns, Array(Empty, Empty, sTabela))
      if not oColumnRs.EOF then
    %>
  <table border="0" cellspacing="0" cellpadding="2" width="90%" align="center">
    <tr bgcolor="#CCCCCC">
      <td colspan="7" align="center" class="tblTituloX"><%=sTabela%></td>
    </tr>
    <tr bgcolor="#E7E7E7">
      <td class="tblTitulo0" nowrap>CAMPO</td>
      <td class="tblTitulo1" nowrap>TIPO</td>
      <td class="tblTitulo1" nowrap>TAMANHO</td>
      <td class="tblTitulo1" nowrap>PRECIS&Atilde;O</td>
      <td class="tblTitulo1" nowrap>ESCALA</td>
      <td class="tblTitulo1" nowrap>PERMITIR NULO</td>
      <td class="tblTitulo1" nowrap>VALOR PADR&Atilde;O</td>
    </tr>
    <%
       do while not oColumnRs.EOF
    %>
    <tr>
      <td class="tblCelula0" nowrap><%=oColumnRs("Column_Name")%>&nbsp;</td>
      <td class="tblCelula1" nowrap><%response.write FieldType(oColumnRs("Data_Type"),_
           oColumnRs("Character_Maximum_Length"),oColumnRs("Numeric_Precision"))&_
           "("&oColumnRs("Data_Type")&")"%>&nbsp;</td>
      <td class="tblCelula1" nowrap><%=oColumnRs("Character_Maximum_Length")%>&nbsp;</td>
      <td class="tblCelula1" nowrap><%=oColumnRs("Numeric_Precision")%>&nbsp;</td>
      <td class="tblCelula1" nowrap><%=oColumnRs("Numeric_Scale")%>&nbsp;</td>
      <td class="tblCelula1" align="center" nowrap>
      <% if oColumnRs("Is_Nullable") then %>
        <input type="checkbox" name="ckbNulo" checked>
      <% else %>
        <input type="checkbox" name="ckbNulo">
      <% end if %>
      </td>
      <td class="tblCelula1" nowrap><%=oColumnRs("Column_Default")%>&nbsp;</td>
    </tr>
  <%


       oColumnRs.MoveNext
       Loop


       oColumnRs.Close
       Set oColumnRs = Nothing


       oConn.Close
       Set oConn = Nothing
  %>
  </table>
  <%


     end if
    end if
   end if
%>
</form>
</body>
</html>



Esta matéria foi postada originalmente no ASP4Developers por Rafael "M4yers" Rossi (site), que na época era "...". Hoje, vai saber...

 

Marcadores:

0 comentários:

Postar um comentário

Assinar: Postar comentários (Atom)