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

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.

Contributed by
Rating: 4 stars4 stars4 stars4 stars4 stars / 15
February 07, 2006
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

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.

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

To work with the data table in the previous section, I created a separate “form” with the following code:

Imports Microsoft.SqlServer.Management.Smo
Public Class Form3
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles Button1.Click
        Dim svr As Server = New Server(".\sql2k5")
        Dim db As Database
        Dim dtReport As DataTable = util.getStructDatabaseBasic
        For Each db In svr.Databases
            Dim drReport As DataRow = dtReport.NewRow
            drReport ("Name") = db.Name
            drReport ("Tables") = db.Tables.Count
            drReport ("Views") = db.Views.Count
            drReport ("StoredProcedures") = db.StoredProcedures.Count
            drReport ("UserDefinedDataTypes") =
db.UserDefinedDataTypes.Count
            drReport ("UserDefinedAggregates") =
db.UserDefinedAggregates.Count
            drReport ("UserDefinedFunctions") =
db.UserDefinedFunctions.Count
            drReport ("UserDefinedTypes") = db.UserDefinedTypes.Count
            drReport ("Synonyms") = db.Synonyms.Count
            drReport ("Triggers") = db.Triggers.Count
            dtReport.Rows.Add(drReport)
        Next
        Me.DataGridView1.DataSource = dtReport
    End Sub
End Class

The most important statements from the above code fragment are the following statements:

Imports Microsoft.SqlServer.Management.Smo
….
        Dim svr As Server = New Server(".\sql2k5")
        Dim db As Database
       
        For Each db In svr.Databases

The first statement imports the necessary SMO namespace (which maintains the central SMO hierarchy).  The rest of the statements mainly connect to a named instance available within the local computer, and retrieve all database-related information into “db” for each iteration of the “FOR” loop.   

The collection “databases” holds all information about the databases existing within an SQL Server instance.  This needs to be invoked using a server object (in this case, it is “svr”).

How do we retrieve advanced info on all databases from a SQL Server instance using SMO?

Previous sections discussed dealing with only basic information (generally needed by programmers).  Now, in this section, I shall further extend what we are doing with some “advanced” information (generally needed by DBAs) related to every database.

Before proceeding further, we again needed to define a new structure to hold the advanced database information.  Let us look into that.

Public Function getStructDatabaseAdvanced() As DataTable
        Dim dt As New DataTable
        With dt.Columns
            .Add (New DataColumn ("Name"))
            .Add (New DataColumn ("Status"))
            .Add (New DataColumn ("PrimaryFilePath"))
            .Add (New DataColumn ("Size"))
            .Add (New DataColumn ("SpaceAvailable"))
            .Add (New DataColumn ("DataSpaceUsage"))
            .Add (New DataColumn ("ActiveConnections"))
            .Add (New DataColumn ("Assemblies"))
            .Add (New DataColumn ("DatabaseGuid"))
            .Add (New DataColumn ("ExtendedStoredProcedures"))
            .Add (New DataColumn ("FileGroups"))
            .Add (New DataColumn ("FullTextCatalogs"))
            .Add (New DataColumn ("LogFiles"))
            .Add (New DataColumn ("LastBackupDate"))
        End With
        Return dt
    End Function

This is very similar to the one I defined in the first section.  Now, let us proceed with the code to retrieve the information.

Dim svr As Server = New Server(".\sql2k5")
        Dim db As Database
        Dim dtReport As DataTable = util.getStructDatabaseAdvanced
        For Each db In svr.Databases
            Dim drReport As DataRow = dtReport.NewRow
            drReport ("Name") = db.Name
            drReport ("Status") = db.Status
            drReport ("PrimaryFilePath") = db.PrimaryFilePath
            drReport ("Size") = db.Size
            drReport ("SpaceAvailable") = db.SpaceAvailable
            drReport ("DataSpaceUsage") = db.DataSpaceUsage
            drReport ("ActiveConnections") = db.ActiveConnections
            drReport ("Assemblies") = db.Assemblies.Count
            drReport ("DatabaseGuid") = db.DatabaseGuid
            drReport ("ExtendedStoredProcedures") =
db.ExtendedStoredProcedures.Count
            drReport ("FileGroups") = db.FileGroups.Count
            drReport ("FullTextCatalogs") = db.FullTextCatalogs.Count
            drReport ("LogFiles") = db.LogFiles.Count
            drReport ("LastBackupDate") = db.LastBackupDate
            dtReport.Rows.Add(drReport)
        Next
        Me.DataGridView1.DataSource = dtReport
    End Sub

How do we retrieve security info about all databases from a SQL Server instance using SMO?

Previous sections discussed dealing with basic and advanced information about databases.  Now, in this section, I shall further extend what we are doing with some “security” information related to every database.

Before proceeding further, we again need to define a new structure to hold the database information.  Let us look into that.

Public Function getStructDatabaseSecurity() As DataTable
        Dim dt As New DataTable
        With dt.Columns
            .Add (New DataColumn ("Name"))
            .Add (New DataColumn ("Schemas"))
            .Add (New DataColumn ("Users"))
            .Add (New DataColumn ("Roles"))
            .Add (New DataColumn ("ApplicationRoles"))
            .Add (New DataColumn ("Certificates"))
         
        End With
        Return dt
    End Function

This is very similar to the one I defined in the first section.  Now, let us proceed with the code to retrieve the information.

Imports Microsoft.SqlServer.Management.Smo
Public Class Form5
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles Button1.Click
        Dim svr As Server = New Server(".\sql2k5")
        Dim db As Database
        Dim dtReport As DataTable = util.getStructDatabaseSecurity
        For Each db In svr.Databases
            Dim drReport As DataRow = dtReport.NewRow
            drReport ("Name") = db.Name
            drReport ("Schemas") = db.Schemas.Count
            drReport ("Users") = db.Users.Count
            drReport ("Roles") = db.Roles.Count
            drReport ("ApplicationRoles") = db.ApplicationRoles.Count
            drReport ("Certificates") = db.Certificates.Count
            dtReport.Rows.Add (drReport)
        Next
        Me.DataGridView1.DataSource = dtReport
    End Sub
End Class

How do we retrieve table information from a SQL Server database using SMO?

This section will be a bit different from the previous sections.  Here, I need to provide a list of all existing databases as a dropdown.  When the user selects a database and clicks on a button, it should retrieve all tables and related information from the selected database.

Just to retrieve the database list, I constructed a separate routine in “util.vb” as follows:

Public Function getDatabaseList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server) As DataTable
        Dim dt As New DataTable
        With dt.Columns
            .Add ("DatabaseName")
        End With
 
        For Each db As Microsoft.SqlServer.Management.Smo.Database In
svr.Databases
            Dim dr As DataRow = dt.NewRow
            Dr ("DatabaseName") = db.Name
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

After completing the above routine, I proceeded with the following “form load” event to populate the list of database names into the dropdown.

Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
        Dim svr As Server = New Server(".\sql2k5")
        Me.ComboBox1.DataSource = util.getDatabaseList(svr)
        Me.ComboBox1.DisplayMember = "DatabaseName"
    End Sub

Okay, the code above mainly covered getting database names into a dropdown list.  Now I shall further extend what we are doing to retrieve table information for the selected database.

Before proceeding further, we again needed to define a new structure to hold the table information.  Let us look into that.

Public Function getStructTableBasic() As DataTable
        Dim dt As New DataTable
        With dt.Columns
            .Add (New DataColumn("TableName"))
            .Add (New DataColumn("Columns"))
            .Add (New DataColumn("Indexes"))
            .Add (New DataColumn("RowCount"))
            .Add (New DataColumn("Triggers"))
        End With
        Return dt
    End Function

This is very similar to the one I defined in the first section.  Now, let us proceed with the code to retrieve the information.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
 
        Dim svr As Server = New Server(".\sql2k5")
        Dim db As Database = svr.Databases(Me.ComboBox1.Text)
        Dim tbl As Table
        Dim dtReport As DataTable = util.getStructTableBasic
        For Each tbl In db.Tables
            Dim drReport As DataRow = dtReport.NewRow
            drReport ("TableName") = tbl.Name
            drReport ("Columns") = tbl.Columns.Count
            drReport ("Indexes") = tbl.Indexes.Count
            drReport ("RowCount") = tbl.RowCount
            drReport ("Triggers") = tbl.Triggers.Count
            dtReport.Rows.Add (drReport)
        Next
        Me.DataGridView1.DataSource = dtReport
    End Sub

From the above code fragment, I selected only a particular database using the following statement:

        Dim db As Database = svr.Databases(Me.ComboBox1.Text)

Summary

Apart from the above information I explained (related to databases and tables), there exists a lot more information you can retrieve from the server, database and table.  But for the sake of keeping this article simple, I included only the most important ones. 

You can use all of my code and design; think of it as a sort of “SMOLibrary” and reuse it in several applications.  If you need any further interesting topics on SMO, please do not hesitate to post.

The entire demonstration solution has been developed using SQL Server 2005 Enterprise Edition and Visual Studio 2005 Professional Edition on Windows Server 2003 Standard Edition. Please note that I didn’t really test the solution on any other versions/editions of the similar suite of Microsoft products. 

Any comments, suggestions, ideas, improvements, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.

blog comments powered by Disqus
MS SQL SERVER ARTICLES

- Idera Releases SQL Diagnostic Manager v7.1
- MS SQL Sever 2012 Launch, New Idera Release
- OpenText Azure Cloud Solution, Geminaire Raa...
- Melissa Data Releases MatchUp Tool for SQL S...
- Glovia`s G2 ERP Solution to Support SQL Serv...
- Upgrade Assistant for SQL Server 2012 Releas...
- Azure Update Features Several New Improvemen...
- NT OBJECTives SQL Invader Tool Offers Free V...
- SQL Server ODBC Driver for Red Hat Enterpris...
- Heroku Postgres: A New SQL Database-as-a-Ser...
- Idera Compliance Manager 3.5 and SQL Server ...
- Microsoft and Joyent Announce Node.js Window...
- How to Install Xampp on Windows XP
- SQL Server 2008 SP3 and HP Database Enterpri...
- How To Install Windows Azure

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 8 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials