Retrieving SQL Server 2005 Database Info Using SMO: Database Info, Table Info

(Page 1 of 5 )

This article is the second in a series focusing on retrieving SQL Server 2005 information using SMO along with Visual Basic 2005 and Visual Studio 2005. With the basics covered in the previous article, this one goes right into technical information.

---

A downloadable file for this article is available here.

---

In this article, we mainly deal with the following types of information:

• Basic database information
• Advanced database information
• Security information related to the database
• Table information from a selected database

Since I already explained some points about SMO, DMO and so on in my previous article, I shall dive right into the technical issues of using SMO. I also covered the concept of “working with SMO using Visual Studio 2005” in my previous article.  If you are new to SMO (or having any trouble working with a new Visual Studio 2005 solution), I strongly suggest you go through that article.

Creating our own structure to hold basic database information

The following function, “getStructDatabaseBasic” is one of the routines which is defined in “util.vb” (from the downloadable zip) to mainly hold basic database information.

Public Function getStructDatabaseBasic() As DataTable

        Dim dt As New DataTable

        With dt.Columns

            .Add (New DataColumn ("Name"))

            .Add (New DataColumn ("Tables"))

            .Add (New DataColumn ("Views"))

            .Add (New DataColumn ("StoredProcedures"))

            .Add (New DataColumn ("UserDefinedDataTypes"))

            .Add (New DataColumn ("UserDefinedAggregates"))

            .Add (New DataColumn ("UserDefinedFunctions"))

            .Add (New DataColumn ("UserDefinedTypes"))

            .Add (New DataColumn ("Synonyms"))

            .Add (New DataColumn ("Triggers"))

        End With

        Return dt

    End Function

It mainly creates a data table with a column structure on basic information, about a particular database existing in an SQL Server instance.  The column “Name” holds the database's name. The column “Tables” holds the number of tables in the database. The column “Views” hold the number of views in the database, and so on for the rest of the columns.

Next: How do we retrieve basic info about all databases from a SQL Server instance using SMO? >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: Retrieving SQL Server 2005 Database Info Using SMO: Database Info, Table Info

· Hello guys, another contribution on the latest SQL Server 2005 SMO. Any doubts,...   >>> Post your comment now!