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.
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.
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”).
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
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
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
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.
MS SQL Server 
/ 15 
Retrieving SQL Server 2005 Database Info using SMO: Scripting Tables, Views, Sto
