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.

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.

Speak Your Mind

*