Get All SQL Database Table Names and Fields Using VB.NET

.Net Framework Logo

Image via Wikipedia

VB.NET Problem

You need to see (write out on your browser’s screen) all of the table names and field names in your back end SQL database and the application is written in .NET, specifically VB.NET on an .aspx page.

VB.NET Resolution

Create a page that uses a connection to your database, the database schema, and the tables it finds to loop through and write the results your browser window.

Background

I came across this need working on a client’s site recently and spent too long searching .NET programming forums for answers. For one reason or another, all of the solutions I encountered didn’t work in my situation. So, here’s another option that will hopefully save you the time.

Copy this file, change the connection string, save it, and upload it to your server.

As always, feel free to contact me with any questions.

Using this code means you agree it is at your own risk, and so forth.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Testing</title>
</head>

<body>

<script type="text/vb" runat="server">
sub showSchema()
 Dim constring as string = "paste your connection string here, it will look something like:
     Data Source=data.source.com; Initial Catalog=DbaseName; User ID=someuser; Password=somepassword;"
 Dim NewConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(constring)
 dim query as string = "select * from INFORMATION_SCHEMA.tables"
 dim myCmd as SqlDataAdapter = New SqlDataAdapter(query,NewConnection)
 dim myData as new DataSet()
 myCmd.Fill(myData) 

 for each table as DataTable in myData.Tables
 for each row as DataRow in table.Rows
 for each col as DataColumn in table.Columns
 Response.Write ( row ( col ).ToString ( ) )
 Response.Write ("<br>")
 next
 next
 next
end sub

sub showTables()
 Dim NewConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection("paste your connection string here,
   it will look something like:
   Data Source=data.source.com; Initial Catalog=DbaseName; User ID=someuser; Password=somepassword;")
 dim query as string = "select * from INFORMATION_SCHEMA.tables"
 dim myCmd as SqlDataAdapter = New SqlDataAdapter(query,NewConnection)
 dim myData as new DataSet()
 myCmd.Fill(myData)

 dim tableName as string

 for each table as DataTable in myData.Tables
 for each row as DataRow in table.Rows
 Response.Write ( "<h2>" & row ( 2 ).ToString ( ) & "</h2>" )
 tableName = ( row ( 2 ).ToString ( ) )
 'get table data
 dim query2 as string = "select * from " & tableName
 dim myCmd2 as SqlDataAdapter = New SqlDataAdapter(query2,NewConnection)
 dim myData2 as new DataSet()
 myCmd2.Fill(myData2)
 for each table2 as DataTable in myData2.Tables
 'for each row2 as DataRow in table2.Rows
 for each col2 as DataColumn in table2.Columns
 Response.Write ( ( col2 ).ToString ( ) )
 Response.Write ("<br>")
 next
 'next
 next
 next
 next
end sub
</script>
<% showSchema %>
<% showTables %>

</body>
</html>

Comments

  1. Hello sir

    very good solution but it gives only master database’ tables if i want to fetch another database then what i have to do

  2. Thanks for your question @param. I’m not sure if I understand you though.

    To get another database’s tables, you would simply change the connection string to that of the other database.

    If you want to get more than one database’s tables at the same time, you could copy the function, rename it, update the second database connection string, and paste it below the first <%showTables%>.

    Or, to make it more user-friendly, you could edit the function, making the connection string a variable and then pass that string into the function like <%showTables(connectionString)%>. Then you could call the showTables function for as many databases as you’d like.

    Is this what you meant?

  3. Deepak Puri says

    That was very helpful, thanks

  4. Worked perfect. Thank you Aaron.

    • I tried to run the table subroutine in vb.net 2008 and get an error at myCmd2.Fill(myData2). Any help? The exception details are listed below:

      System.Data.SqlClient.SqlException was unhandled
      Class=15
      ErrorCode=-2146232060
      LineNumber=1
      Message=Incorrect syntax near the keyword ‘and’.
      Number=156
      Procedure=””
      Server=\\.\pipe\75F8CFD2-710E-4E\tsql\query
      Source=.Net SqlClient Data Provider
      State=1
      StackTrace:
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
      at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
      at System.Data.SqlClient.SqlDataReader.get_MetaData()
      at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
      at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
      at Database_Example.TableList.showTables() in C:\Documents and Settings\10031199\My Documents\Visual Studio 2010\Projects\Database\TableList.vb:line 43
      at Database_Example.MainForm2.btnTables_Click(Object sender, EventArgs e) in C:\Documents and Settings\10031199\My Documents\Visual Studio 2010\Projects\Database\MainForm2.vb:line 8
      at System.Windows.Forms.Control.OnClick(EventArgs e)
      at System.Windows.Forms.Button.OnClick(EventArgs e)
      at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
      at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
      at System.Windows.Forms.Control.WndProc(Message& m)
      at System.Windows.Forms.ButtonBase.WndProc(Message& m)
      at System.Windows.Forms.Button.WndProc(Message& m)
      at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
      at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
      at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
      at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
      at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
      at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
      at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
      at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
      at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
      at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
      at Database_Example.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
      at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
      at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
      at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
      at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
      at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
      at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
      at System.Threading.ThreadHelper.ThreadStart()
      InnerException:

      • Hello es634. I am out of my depth when it comes to .NET. Hopefully one of the other commenters here or someone else who sees this can pitch in to answer you.