Web Report of SQL Table and View Structures via ASP

Here’s a fairly basic ASP example for displaying the table and view structures in a SQL database.  I have only tested this with MS SQL Server 2005 and 2008, so I can’t vouch for other database platforms.  Be sure to edit the variables at the top (in red) carefully.

'****************************************************************
' Filename..: sqlschema.asp
' Author....: skatterbrainz (skatterbrainz.blogspot.com)
' Date......: 08/04/2009
' Purpose...: display tables and views
' SQL.......: SERVER\INSTANCE, DatabaseName
'****************************************************************
Response.Expires = -1

Const schema_name = "dbo"
Const db_server = "SERVERNAME\INSTANCE"
Const db_name   = "DatabaseName"
Const db_user   = "UserName"
Const db_pwd    = "Pa$$worD"

dsn = "DRIVER=SQL Server;SERVER="&db_server&";database="&db_name&";UID="&db_user&";PWD="&db_pwd&";"

'----------------------------------------------------------------

Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adUseClient = 3
Const adCmdText = &H0001

'----------------------------------------------------------------
' function: trap and display error and stop processing
'----------------------------------------------------------------

Sub ErrTrap(s)
    If err.Number <> 0 Then
        wscript.echo "error: " & err.Number & " / " & err.Description
        wscript.echo "reason: " & s
        Response.End
    End If
End Sub

On Error Resume Next    

Set conn = Server.CreateObject("ADODB.Connection")
Set cmd  = Server.CreateObject("ADODB.Command")
Set rs   = Server.CreateObject("ADODB.Recordset")

query = "SELECT table_name, table_type " & _
    "FROM information_schema.tables " & _
    "WHERE table_schema='" & schema_name & "' " & _
    "ORDER BY table_name"

conn.Open dsn
ErrTrap "fail: ado-conn-open"

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly

Set cmd.ActiveConnection = conn

cmd.CommandType = adCmdText
cmd.CommandText = query
rs.Open cmd
ErrTrap "fail: ado-rs-open"

If rs.BOF And rs.EOF Then
    rs.Close
    conn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing
    Response.Write "<strong>No records found</strong>"
    Response.End
End If

'----------------------------------------------------------------
' function: 
'----------------------------------------------------------------

Sub TableColumns(tableName)
    Dim conn, cmd, rs, query
    On Error Resume Next    
    
    Set conn = Server.CreateObject("ADODB.Connection")
    Set cmd  = Server.CreateObject("ADODB.Command")
    Set rs   = Server.CreateObject("ADODB.Recordset")
    
    query = "SELECT column_name, data_type, ordinal_position, column_default, " & _
        "character_maximum_length AS maxlen, is_nullable, numeric_precision " & _
        "FROM information_schema.columns " & _
        "WHERE table_schema='" & schema_name & "' AND table_name='" & tableName & "'"
    
    conn.Open dsn
    ErrTrap "fail: ado-conn-open columns"
    
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    rs.LockType = adLockReadOnly
    
    Set cmd.ActiveConnection = conn
    
    cmd.CommandType = adCmdText
    cmd.CommandText = query
    rs.Open cmd
    ErrTrap "fail: ado-rs-open columns"
    
    If Not(rs.BOF And rs.EOF) Then
        Response.Write "<table width=100% border=1 cellpadding=4 cellspacing=1>"
        Response.Write "<tr><td>Field</td><td>Ordinal</td>"
        Response.Write "<td>Type</td><td>Size</td><td>Null</td>"
        Response.Write "<td>Default</td><td>Prec</td>"
        Response.Write "</tr>" & vbCRLF
        Do Until rs.EOF
            ordinal  = rs("ordinal_position").value
            colname  = rs("column_name").value
            datatype = rs("data_type").value
            maxlen   = rs("maxlen").value
            nullable = rs("is_nullable").value
            numprec  = rs("numeric_precision").value
            coldef   = rs("column_default").value
            Response.Write "<tr>" & vbCRLF
            Response.Write "<td style=width:180px>" & rs("column_name").value & "</td>"
            Response.Write "<td style=width:60px>" & rs("ordinal_position").value & "</td>"
            Response.Write "<td style=width:120px>" & rs("data_type").value & "</td>"
            Response.Write "<td style=width:120px>"
            If Not(IsNull(rs("maxlen").value)) Then
                Response.Write rs("maxlen").value
            End If
            Response.Write "</td>" & vbCRLF
            Response.Write "<td class=v8w style=width:120px>"
            If rs("is_nullable").value = "YES" Then
                Response.Write " [Null]"
            End If
            Response.Write "</td>" & vbCRLF
            Response.Write "<td style=width:120px>" & rs("column_default").value & "</td>" & vbCRLF
            Response.Write "<td>" & rs("numeric_precision").value & "</td>" & vbCRLF
            Response.Write "</tr>" & vbCRLF
            rs.MoveNext
        Loop
        Response.Write "</table>" & vbCRLF
        rs.Close
        conn.Close
        Set rs = Nothing
        Set cmd = Nothing
        Set conn = Nothing
    End If
End Sub

'----------------------------------------------------------------
' function: 
'----------------------------------------------------------------

Sub ViewTables(vName)
    Dim conn, cmd, rs, query
    On Error Resume Next    
    
    Set conn = Server.CreateObject("ADODB.Connection")
    Set cmd  = Server.CreateObject("ADODB.Command")
    Set rs   = Server.CreateObject("ADODB.Recordset")
    
    query = "SELECT table_name, column_name " & _
        "FROM information_schema.view_column_usage " & _
        "WHERE table_schema='" & schema_name & "' AND view_name='" & vName & "'"
    
    conn.Open dsn
    ErrTrap "fail: ado-conn-open columns"
    
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    rs.LockType = adLockReadOnly
    
    Set cmd.ActiveConnection = conn
    
    cmd.CommandType = adCmdText
    cmd.CommandText = query
    rs.Open cmd
    ErrTrap "fail: ado-rs-open columns"
    
    If Not(rs.BOF And rs.EOF) Then
        Response.Write "<table width=100% border=1 cellpadding=4 cellspacing=1>"
        Response.Write "<tr><td>Table</td><td>Column</td>"
        Response.Write "</tr>" & vbCRLF
        Do Until rs.EOF
            ordinal  = rs("ordinal_position").value
            colname  = rs("column_name").value
            datatype = rs("data_type").value
            maxlen   = rs("maxlen").value
            nullable = rs("is_nullable").value
            numprec  = rs("numeric_precision").value
            coldef   = rs("column_default").value
            Response.Write "<tr>" & vbCRLF
            Response.Write "<td style=width:180px>'" & rs("table_name").value & "</td>"
            Response.Write "<td>" & rs("column_name").value & "</td>"
            Response.Write "</tr>" & vbCRLF
            rs.MoveNext
        Loop
        Response.Write "</table>" & vbCRLF
        rs.Close
        conn.Close
        Set rs = Nothing
        Set cmd = Nothing
        Set conn = Nothing
    End If
End Sub

'----------------------------------------------------------------

Do Until rs.EOF
    If rs("table_type").value = "BASE TABLE" Then
        Response.Write "<h4>" & rs("table_name").Value & "</h4>" & vbCRLF
        TableColumns rs("table_name").Value
    End If
    rs.MoveNext
Loop

rs.MoveFirst
Do Until rs.EOF
    If rs("table_type").value = "VIEW" Then
        Response.Write "<h4>" & rs("table_name").Value & "</h4gt;" & vbCRLF
        TableColumns rs("table_name").value
        ViewTables rs("table_name").value
        Response.Write "<br/>"
    End If
    rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing