Home » Web Development » Classic ASP: Getting MySQL DB Schema Information

Classic ASP: Getting MySQL DB Schema Information

Difficulty Level    

You may have been able to tell by now that I’m a fan of Classic ASP, err…. Active Server Pages.  I love how simple they are to learn, use and how much you can do with them to bring functionality to the web.  I usually use MySQL as the database I pull stuff from on the webpages I make and think this code might be helpful.  It will list all the Databases you’ve been given access to and Tables within those databases.  You can easily expand the code to list the columns in the tables, add tables, drop tables, etc…

    Const adOpenKeyset = 1
	Const adOpenDynamic = 2
	Const adLockPessimistic = 2
	Const adCmdText = 1
	Const adCmdStoredProc = 4

    Set MyConn = Server.CreateObject("ADODB.Connection") 'Declare a connection
    Set RS = Server.CreateObject("ADODB.Recordset") ' Declare recordset
	MyConn.Open "twt" 'ODBC database we want to use
	'** Get the databases on the server
	Set RS = MyConn.EXECUTE("Show Databases",, adCmdText)
	'**Create Table for better viewing pleasure
	response.write "<table border=""1"">"
	'** Loop through the databases
	While not RS.EOF
		'**Create Table row with Database name on its own line
		Response.write "<tr><td colspan=""2"">" & RS.Fields(0).name & " : " & RS.Fields(0) & "</td></tr>"
		'**Get the tables in the database
		Set objTables = MyConn.Execute("SHOW TABLES FROM " & RS.Fields(0),, adCmdText)
		'**Make Cell with Tables: on the left and the table names from database on the right
		Response.write "<tr><td valign=""top"">Tables:</td><td>"
		While not objTables.EOF
			Response.write "   " & objTables.Fields(0) & "<br>"
		'**End table names cell and row 
		Response.write "</td></tr>"
	'**On to next database (if it exists)
	'**Close out the table with the proper tag once all data has been pulled and displayed
	Response.write "</table>"