Fórum Information_schema - Porque Não Funciona ? ? ? #23055

<¬
Set Conexao = Server.CreateObject("Adodb.Connection")
Conexao.ConnectionString = "DBQ=" & Server.MapPath("../../databases/123/123db.mdb") & ";"
Conexao.ConnectionString = Conexao.ConnectionString & "Driver={Microsoft Access Driver (*.mdb)};uid=Admin; password=XXXXXXX"
Conexao.Open


Sub RenderTableConstraints(strTableName, conexao)

  Dim strQuery
  Dim strConstraintName, strConstraintType, strConstraintDetails
  Dim oRsConstraintList
  
  ´--- Get the list of constraints on the table.
  strQuery = "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=´" + strTableName + "´"
  Set oRsConstraintList = conexao.Execute(strQuery)

  Response.Write("<BR><BR><B>Constraints</B>")
    
  ´--- Start a table for the list of columns.

  Response.Write("<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=2 WIDTH=100¬ Class=Normal bordercolor=Navy>")

  Response.Write("<TR>")
  Response.Write("<TD WIDTH=´25¬´>Constraint Type</TD>")
  Response.Write("<TD WIDTH=´35¬´>Constraint Name</TD>")
  Response.Write("<TD WIDTH=´40¬´>Fields(PK,FK) / Clause(CHECK)</TD>")
  Response.Write("</TR>")
  
  ´--- Loop to parse to the list of columns.
  While (Not oRsConstraintList.EOF)
  
    ´--- Store the constraint name and type.
    strConstraintName = oRsConstraintList("CONSTRAINT_NAME")
    strConstraintType = oRsConstraintList("CONSTRAINT_TYPE")
    
    ´--- Get constraint details based on the constraint type.
    If (strConstraintType = "CHECK") Then
      strConstraintDetails = GetClauseForConstraint(strConstraintName, conexao)
    Else
      strConstraintDetails = GetColumnsForConstraint(strConstraintName, strTableName, conexao)
    End If
  
    ´--- Output the details of each column.
    Response.Write("<TR>")
    Response.Write("<TD>" + strConstraintType + "</TD>")
    Response.Write("<TD>" + strConstraintName + "</TD>")
    Response.Write("<TD>" + strConstraintDetails + "</TD>")
    Response.Write("</TR>")    
     
    ´--- Move to the next column.
    oRsConstraintList.MoveNext
  Wend
    
  ´--- End the table for columns.
  Response.Write("</TABLE><br><br>")
  
  ´--- Clean up.
  Set oRsConstraintList = Nothing
End Sub

Function GetClauseForConstraint(strConstraintName, conexao)

  Dim strQuery
  Dim oRs
  
  ´--- Build query to get the column list for constraint.
  strQuery = "SELECT c.CHECK_CLAUSE " + _
             "FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS c " + _
             "WHERE ( c.constraint_name = ´" + strConstraintName + "´)"
    
  ´--- Use connection and get the recordset.
  Set oRs = conexao.Execute(strQuery)
  
  ´--- Return the string.
  GetClauseForConstraint = oRs("CHECK_CLAUSE")
  
End Function


´------------------------------------------------------------------
´--- To get the list of cols associated with constraint as , delimited str.
´------------------------------------------------------------------
Function GetColumnsForConstraint(strConstraintName, strTableName, conexao)

  Dim iCount
  Dim strQuery, strFldNames
  Dim oRs
  

  ´--- Build query to get the column list for constraint.
  strQuery = "SELECT d.column_name " + _
             "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE d " + _
             "WHERE (d.constraint_name = ´" + strConstraintName + " ´) " + _
             "ORDER BY d.ordinal_position"
    
  ´--- Use connection and get the recordset.
  Set oRs = conexao.Execute(strQuery)
  
  ´--- Initialize the field names string.
  strFldNames = "": iCount = 0
  
  ´--- Loop to build a comma delimited string.
  While (Not oRs.EOF)
    ´--- Add a comma separator between 2 column names.
    If (iCount > 0) Then strFldNames = strFldNames + ", "
    
    ´--- Add the next field name.
    strFldNames = strFldNames + oRs("column_name")
  
    ´--- Move to next row and increment counter.
    oRs.MoveNext: iCount = iCount + 1
  Wend
  
  ´--- Return the column names as a comma delimited string.
  GetColumnsForConstraint = strFldNames
  
End Function

conexao.Close
Set conexao = Nothing

¬>