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
No comments:
Post a Comment