Getting A List of Tables From SQL Server: 2 MethodsDescription:
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()
cnString = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial " & _
"Catalog=" & strDatabase & ";" & _
"User ID=" & strUser & ";" & _
"Password=" & strPwd & ";"
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
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="TableList.aspx.vb" Inherits="Sandbox.TableList"%>
<!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()
cnString = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial " & _
"Catalog=" & strDatabase & ";" & _
"User ID=" & strUser & ";" & _
"Password=" & strPwd & ";"
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
End Class
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP.NET Articles
More By aspfree
 developerWorks - FREE Tools!
|
Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity.
FREE! Go There Now!
|
|
|
|
Because access to government information continues to be an area of concern for many U.S. citizens with disabilities, the U.S. government enacted Section 508 of the Rehabilitation Act in 2001 to ensure that government agencies create accessible Web content, enabling all citizens to access the information they need. A fully accessible Web site makes Web content accessible to all individuals, including those with disabilities, who may be accessing Web content via a variety of user agents. Common user agents include standard Web browsers, text-only browsers, assistive devices and mobile devices such as cell phones or personal digital assistants (PDAs).
FREE! Go There Now!
|
|
|
|
Get a free trial download of IBM Lotus Forms V3.0 (formerly Workplace Forms), which provides a zero-footprint eForms solution to help you automate and move forms-based business processes off the desktop and onto the Web. With Lotus Forms, you can extend applications beyond the firewall by creating a single electronic form document ready for use in both thick and Web 2.0 thin client format.
FREE! Go There Now!
|
|
|
|
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
FREE! Go There Now!
|
|
|
|
CakePHP is a stable production-ready, rapid-development aid for building Web sites in PHP. This "Cook up Web sites fast with CakePHP" series shows you how to build an online product catalog using CakePHP.
FREE! Go There Now!
|
|
|
|
Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle.
FREE! Go There Now!
|
|
|
|
As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
FREE! Go There Now!
|
|
|
|
Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing.
FREE! Go There Now!
|
|
|
|
Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
FREE! Go There Now!
|
|
|
|
Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
