Database Code
  Home arrow Database Code arrow Getting A List of Tables From SQL Server
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE CODE

Getting A List of Tables From SQL Server
By: Curtis_Swartzentruber
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 36
    2003-01-01

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Description: It is often helpful to be able to query SQL Server to find out the schema for a certain database, including tables, information about columns in a particular table, the text of a stored procedure and so forth. This comes in handy when building applications ...


    Description:

    It is often helpful to be able to query SQL Server to find out the schema for a certain database, including tables, information about columns in a particular table, the text of a stored procedure and so forth. This comes in handy when building applications like code generators or query builders.

    The SQL-DMO Object Library was the main way to interface with SQL Server prior to the .NET Framework. This is still quite a useful COM object and can be used quite successfully from .NET using Interop. In fact, I built quite a nice code-generator using SQL-DMO to build my data access layer in ADO.NET.

    The .NET Framework itself provides some other ways to get at database schema. If using the SqlClient, you can query the INFORMATION_SCHEMA tables or use other methods like "sp_helptext". If using System.Data.OleDb, there is an interesting little class called OleDBSchemaGuid that lets us look at all sorts of interesting schema information. Not just in SQL Server either, but I'm going to focus on that since that's what I use.

    To illustrate, here is a very simple comparison using SQL-DMO and OleDBSchemaGuid to return a list of user tables from a database. The code is pretty simple, so I'll just show you the examples and you can run with it from there.

    I am using the Microsoft SQLDMO Object Library 8.0, which is the version that comes with SQL Server 2000. I have also used SQLDMO 7.0 to work with SQL Server 7 databases. This code uses the release version of the .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 Database Code Articles
    More By Curtis_Swartzentruber

     

    IBM® developerWorks developerWorks - FREE Tools!


    Check out the new Jazz space on developerWorks

    <a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts.
    FREE! Go There Now!


    NEW! Best Practices: The Integrated Project and Portfolio Management Platform.

    Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan.
    FREE! Go There Now!


    NEW! Evaluate IBM Rational Developer for System i V7.1

    Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
    FREE! Go There Now!


    NEW! IBM Rational AppScan Standard Edition V7.7

    Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
    FREE! Go There Now!


    NEW! Info 2.0: Harnessing the power of Web 2.0 and Enterprise Mashups

    Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started.
    FREE! Go There Now!


    NEW! Rational Talks to You: Grady Booch on Architecture

    Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
    FREE! Go There Now!


    NEW! Rational Talks to You: Manage RUP-based CMMI initiatives

    Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered!
    FREE! Go There Now!


    NEW! Webcast: Application security testing and Web compliance

    Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development.
    FREE! Go There Now!


    NEW! Webcast: Striking the right balance between manual and automated testing

    Join this webcast to learn how IBM Rational's Functional Testing solution enables you to implement automation your way, at your pace, with your existing staff. In this webcast, you’ll learn how you can eliminate redundancy of manual test scripts, reduce errors, and increase test coverage through test automation. After this presentation you will understand how IBM Rational Functional Testing solution can streamline your manual testing and make test automation easily attainable.
    FREE! Go There Now!


    NEW! Webcast: WebSphere Process Server

    WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    DATABASE CODE ARTICLES

    - Deployment of the MobiLink Synchronization M...
    - MobiLink Synchronization Wizard in SQL Anywh...
    - Finding Matching Records in Data Access Pages
    - Using the AccessDataSource Control in VS 2005
    - A Closer Look at ADO.NET: The Command Object
    - A Closer Look at ADO.NET: The Connection Obj...
    - Using ADO to Communicate with the Database, ...
    - Code Snippets: Counting Records
    - Constraints In Microsoft SQL Server 2000
    - Multilingual entries into a DB and to be dis...
    - Getting A List of Tables From SQL Server
    - SQL Server Database Creator - .NET Version
    - ADO Recordset Paging
    - Two combos, one textbox example
    - Discussion & Listserv Module by Mike Eck...





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 1 Hosted by Hostway
    Stay green...Green IT