WEB LISTING 2: Code that Creates Table with Information About Constraints

Sub RenderTableConstraints(strTableName, oConn)

  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 = oConn.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, oConn)
    Else
      strConstraintDetails = GetColumnsForConstraint(strConstraintName, strTableName, oConn)
    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, oConn)

  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 = oConn.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, oConn)

  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 = oConn.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