Getting A List of Tables From SQL Server: 2 Methods
Description:
It is often helpful to be able to query SQL Server to find out theschema for a certain database, including tables, information aboutcolumns in a particular table, the text of a stored procedure and soforth. This comes in handy when building applications like codegenerators or query builders.
The SQL-DMO Object Library was the main way to interface with SQLServer prior to the .NET Framework. This is still quite a useful COMobject and can be used quite successfully from .NET using Interop. Infact, I built quite a nice code-generator using SQL-DMO to build mydata access layer in ADO.NET.
The .NET Framework itself provides some other ways to get at databaseschema. If using the SqlClient, you can query the INFORMATION_SCHEMAtables or use other methods like "sp_helptext". If usingSystem.Data.OleDb, there is an interesting little class calledOleDBSchemaGuid that lets us look at all sorts of interesting schemainformation. Not just in SQL Server either, but I'm going to focus onthat since that's what I use.
To illustrate, here is a very simple comparison using SQL-DMO andOleDBSchemaGuid to return a list of user tables from a database. Thecode is pretty simple, so I'll just show you the examples and you canrun with it from there.
I am using the Microsoft SQLDMO Object Library 8.0, which is theversion that comes with SQL Server 2000. I have also used SQLDMO 7.0 towork with SQL Server 7 databases. This code uses the release version ofthe .NET Framework and Visual Studio .NET.
Function Using SQLDMO
Private Function LoadUserTables_DMO(ByVal strServer As String, _ ByVal strUser As String, _
ByVal strPwd As String, _ ByVal strDatabase As String) As ArrayList
Dim slTables As ArrayList = New ArrayList() Dim srv As SQLDMO._SQLServer srv = New SQLDMO.SQLServer() Try srv.Connect(strServer, strUser, strPwd) Dim db As SQLDMO.Database = CType(srv.Databases.Item(strDatabase), SQLDMO.Database) Dim tbl As SQLDMO.Table For Each tbl In db.Tables If Not tbl.SystemObject Then slTables.Add(tbl.Name) End If Next srv.DisConnect() srv = Nothing Catch x As Exception lblMsg.Text = x.Message slTables = Nothing End Try
Return slTables
End Function
Function Using OleDBSchemaGuid
Private Function LoadUserTables_Schema(ByVal strServer As String, _ ByVal strUser As String, _ ByVal strPwd As String, _ ByVal strDatabase As String) As ArrayList
Dim cnString As String Dim slTables As ArrayList = New ArrayList()
Dim cn As OleDbConnection = New OleDbConnection(cnString) Try cn.Open() Dim schemaTable As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _ New Object() {Nothing, Nothing, Nothing, "TABLE"}) cn.Close() Dim dr As DataRow
For Each dr In schemaTable.Rows slTables.Add(dr("TABLE_NAME")) Next Catch x As OleDbException lblMsg.Text = x.Message slTables = Nothing End Try
Return slTables
End Function
I put together a little ASPX page to test. It is very simple and should be easy to follow. ASPX page
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <title>TableList</title> <LINK href="Stylesheets/Common.css" type="text/css" rel="stylesheet"> <meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR"> <meta content="Visual Basic 7.0" name="CODE_LANGUAGE"> <meta content="JavaScript" name="vs_defaultClientScript"> <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"> </HEAD> <body> <form id="Form1" method="post" runat="server"> <asp:Label id="lblMsg" runat="server"></asp:Label> <p><asp:validationsummary id="valSum" runat="server" displaymode="BulletList" showsummary="True" showmessagebox="False" headertext="The following errors were found."></asp:validationsummary></p> <p><asp:textbox id="DBServer" runat="server"></asp:textbox>SQL Server to target (ComputerName or IP Address) </p> <p><asp:textbox id="UserName" runat="server"></asp:textbox>UserName </p> <p><asp:textbox id="Password" runat="server"></asp:textbox>Password </p> <p><asp:textbox id="Database" runat="server"></asp:textbox>Database </p> <asp:Panel id="PanDropdowns" runat="server" visible="False"> <P> <asp:dropdownlist id="DdlTables_DMO" runat="server"></asp:dropdownlist>List of tables using DMO (Interop) </P> <P> <asp:dropdownlist id="DdlTables_Schema" runat="server"></asp:dropdownlist>List of tables using OleDBSchemaGuid Class </P> </asp:Panel> <P><asp:button id="BtnLoad" onclick="BtnLoad_Click" runat="server" text="Get Table List"></asp:button></P> <asp:requiredfieldvalidator display="None" id="ValR_Server" runat="server" controltovalidate="DBServer" errormessage="Server is a required field."></asp:requiredfieldvalidator> <asp:requiredfieldvalidator display="None" id="ValR_Database" runat="server" controltovalidate="Database" errormessage="Database is a required field."></asp:requiredfieldvalidator> <asp:requiredfieldvalidator display="None" id="ValR_UserName" runat="server" controltovalidate="UserName" errormessage="UserName is a required field."></asp:requiredfieldvalidator> <asp:requiredfieldvalidator display="None" id="ValR_Password" runat="server" controltovalidate="Password" errormessage="Password is a required field."></asp:requiredfieldvalidator></form> </body> </HTML>
Code Behind for page
Option Strict On Option Explicit On
Imports System Imports SQLDMO Imports System.Data Imports System.Data.OleDb
Public Class TableList Inherits System.Web.UI.Page Protected WithEvents DBServer As System.Web.UI.WebControls.TextBox Protected WithEvents UserName As System.Web.UI.WebControls.TextBox Protected WithEvents Password As System.Web.UI.WebControls.TextBox Protected WithEvents Database As System.Web.UI.WebControls.TextBox Protected WithEvents DdlTables_DMO As System.Web.UI.WebControls.DropDownList Protected WithEvents DdlTables_Schema As System.Web.UI.WebControls.DropDownList Protected WithEvents valSum As System.Web.UI.WebControls.ValidationSummary Protected WithEvents ValR_Server As System.Web.UI.WebControls.RequiredFieldValidator Protected WithEvents ValR_Database As System.Web.UI.WebControls.RequiredFieldValidator Protected WithEvents ValR_UserName As System.Web.UI.WebControls.RequiredFieldValidator Protected WithEvents ValR_Password As System.Web.UI.WebControls.RequiredFieldValidator Protected WithEvents PanDropdowns As System.Web.UI.WebControls.Panel Protected WithEvents lblMsg As System.Web.UI.WebControls.Label Protected WithEvents BtnLoad As System.Web.UI.WebControls.Button
Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here
End Sub
Protected Sub btnLoad_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim blnSuccess As Boolean
Dim slTables As ArrayList = LoadUserTables_DMO(DBServer.Text, UserName.Text, _ Password.Text, Database.Text) If Not (slTables Is Nothing) Then DdlTables_DMO.DataSource = slTables DdlTables_DMO.DataBind() blnSuccess = True End If
Dim slTables2 As ArrayList = LoadUserTables_Schema(DBServer.Text, _ UserName.Text, Password.Text, Database.Text) If Not (slTables2 Is Nothing) Then DdlTables_Schema.DataSource = slTables2 DdlTables_Schema.DataBind() blnSuccess = True End If
If blnSuccess Then PanDropdowns.Visible = True Else PanDropdowns.Visible = False End If
End Sub
Private Function LoadUserTables_DMO(ByVal strServer As String, _ ByVal strUser As String, _ ByVal strPwd As String, _ ByVal strDatabase As String) As ArrayList
Dim slTables As ArrayList = New ArrayList() Dim srv As SQLDMO._SQLServer srv = New SQLDMO.SQLServer() Try srv.Connect(strServer, strUser, strPwd) Dim db As SQLDMO.Database = CType(srv.Databases.Item(strDatabase), SQLDMO.Database) Dim tbl As SQLDMO.Table For Each tbl In db.Tables If Not tbl.SystemObject Then slTables.Add(tbl.Name) End If Next srv.DisConnect() srv = Nothing Catch x As Exception lblMsg.Text = x.Message slTables = Nothing End Try
Return slTables
End Function
Private Function LoadUserTables_Schema(ByVal strServer As String, _ ByVal strUser As String, _ ByVal strPwd As String, _ ByVal strDatabase As String) As ArrayList
Dim cnString As String Dim slTables As ArrayList = New ArrayList()
Dim cn As OleDbConnection = New OleDbConnection(cnString) Try cn.Open() Dim schemaTable As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _ New Object() {Nothing, Nothing, Nothing, "TABLE"}) cn.Close() Dim dr As DataRow
For Each dr In schemaTable.Rows slTables.Add(dr("TABLE_NAME")) Next Catch x As OleDbException lblMsg.Text = x.Message slTables = Nothing End Try
ASP.NET 
/ 7 
Getting A List of Tables From SQL Server
