Retrieve Table Size with SQL Server and ASP.NET 3.5

Posted by: Malcolm Sheridan , on 3/18/2009, in Category ASP.NET
Views: 25462
Abstract: The following article demonstrates how to use two undocumented stored procedures in SQL Server 2000/2005/2008 and ASP.NET 3.5 to retrieve table sizes.
Retrieve Table Size with SQL Server and ASP.NET 3.5
 
The following article demonstrates how to use two undocumented stored procedures in Sql Server 2000/2005/2008 and ASP.NET 3.5 to retrieve table sizes. Recently Suprotim Agarwal posted a great article on Retrieving Database Information using ASP.NET and Sql Server 2005/2008. In this article I’ll show you two undocumented stored procedures that have been in SQL Server since 2000. These stored procedures allow you to execute code against every database and every table without having to write cursors or while loops. These two stored procedures are:

- sp_MSforeachtable

- sp_MSforeachdb

sp_MSforeachdb enumerates through each database and executes a command that you specify as the first parameter. sp_MSforeachtable enumerates through each table in a database and executes a command that you specify as in the parameters. I have found this code very useful when I have been asked to estimate the size and growth of a database over time. 
Open Visual Studio 2008 and choose File > New > Web > ASP.NET Web Application.
 
Open the Default.aspx page and add the following code:
 
<asp:DropDownList ID="cboDatabase" runat="server" AutoPostBack="True"
    onselectedindexchanged="cboDatabase_SelectedIndexChanged">
</asp:DropDownList>
<br /><br />
<asp:GridView ID="grdTableInfo" runat="server">
</asp:GridView>
 
In the above code, there two controls. The DropDownList will contain the list of databases and the GridView will display the size of each table in the selected database. Next we will add a new class that will store information about each table. Name the class TableProperty. Add the following public properties to the new TableProperty class:
 
C#
 
public class TableProperty
{
public string Name { get; set; }
      public string Rows { get; set; }
      public string Reserved { get; set; }
      public string Data { get; set; }
      public string IndexSize { get; set; }
      public string Unused { get; set; }       
}
 
VB.NET
 
Public Class TableProperty
       Private privateName As String
       Public Property Name() As String
             Get
                   Return privateName
             End Get
             Set(ByVal value As String)
                   privateName = value
             End Set
       End Property
       Private privateRows As String
       Public Property Rows() As String
             Get
                   Return privateRows
             End Get
             Set(ByVal value As String)
                   privateRows = value
             End Set
       End Property
       Private privateReserved As String
       Public Property Reserved() As String
             Get
                   Return privateReserved
             End Get
             Set(ByVal value As String)
                   privateReserved = value
             End Set
       End Property
       Private privateData As String
       Public Property Data() As String
             Get
                   Return privateData
             End Get
             Set(ByVal value As String)
                   privateData = value
             End Set
       End Property
       Private privateIndexSize As String
       Public Property IndexSize() As String
             Get
                   Return privateIndexSize
             End Get
             Set(ByVal value As String)
                   privateIndexSize = value
             End Set
       End Property
       Private privateUnused As String
       Public Property Unused() As String
             Get
                   Return privateUnused
             End Get
             Set(ByVal value As String)
                   privateUnused = value
             End Set
       End Property
End Class
 
 
The code above is taking advantage of Automatic properties which are new to C# and VB.NET. Automatic properties allow you to create properties without having to define extra code to get and set the value. This is a real time saver.
 
Next step is to add the code to the Default.aspx page. First we need to add code that will open a SQL connection and execute both stored procedures:
 
C#
 
private void Connect()
{
using (SqlConnection cn = new SqlConnection(Database))
{
            cn.Open();
            FetchDatabases(cn);
            FetchTableData(cn);
      } 
}
 
VB.NET
 
Private Sub Connect()
Using cn As New SqlConnection(Database)
             cn.Open()
                  FetchDatabases(cn)
                  FetchTableData(cn)
End Using
End Sub
 
Since we are making two calls, it makes sense to use the one connection and pass it to the FetchDatabases and FetchTableData methods. The code below executes the sp_MSforeachdb stored procedure and adds each database to the DropDownList:
 
C#
 
private void FetchDatabases(SqlConnection cn)
{
SqlCommand cmd = new SqlCommand("EXEC sp_MSforeachdb @command1='select ''?'''", cn);
      using (SqlDataReader dr = cmd.ExecuteReader())
      {
            cboDatabase.Items.Clear();
            while (dr.Read())
            {
                  if (dr.GetString(0) != "tempdb")
                  {
                        cboDatabase.Items.Add(new ListItem(dr.GetString(0), dr.GetString(0)));
                  }
                  dr.NextResult();
            }
      }            
}
 
VB.NET
 
      Private Sub FetchDatabases(ByVal cn As SqlConnection)
            Dim cmd As New SqlCommand("EXEC sp_MSforeachdb @command1='select ''?'''", cn)
            Using dr As SqlDataReader = cmd.ExecuteReader()
                  cboDatabase.Items.Clear()
                  Do While dr.Read()
                        If dr.GetString(0) <> "tempdb" Then
                              cboDatabase.Items.Add(New ListItem(dr.GetString(0), dr.GetString(0)))
                        End If
                        dr.NextResult()
                  Loop
            End Using
      End Sub   
 
 
In the code above, the first parameter, @command1, is executing select ‘?’. The question mark (?) is the replace marker and will be replaced with the database name inside the stored procedure. Following this we need to add each table in the database to the GridView with the size of the table. The code below executes the sp_MSforeachtable stored procedure and executes the sp_spaceused stored procedure to return the size of the table:
 
C#
 
private void FetchTableData(SqlConnection cn)
{
SqlCommand cmd = new SqlCommand("sp_MSforeachtable @command1='EXEC sp_spaceused ''?''',@whereand='or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1'", cn);
      using (SqlDataReader dr = cmd.ExecuteReader())
      {
            List<TableProperty> tables = new List<TableProperty>();
            while (dr.Read())
            {
                  tables.Add(new TableProperty()
                  {
                        Name = dr.GetString(0),
                        Rows = dr.GetString(1),
                        Reserved = dr.GetString(2),
                        Data = dr.GetString(3),
                        IndexSize = dr.GetString(4),
                        Unused = dr.GetString(5)
                    });
                    dr.NextResult();
            }
            grdTableInfo.DataSource = tables.OrderBy(o => o.Name);
            grdTableInfo.DataBind();
      }
}
 
VB.NET
 
   Private Sub FetchTableData(ByVal cn As SqlConnection)
            Dim cmd As New SqlCommand("sp_MSforeachtable @command1='EXEC sp_spaceused ''?''',@whereand='or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1'", cn)
            Using dr As SqlDataReader = cmd.ExecuteReader()
                  Dim tables As New List(Of TableProperty)()
                  Do While dr.Read()
                        tables.Add(New TableProperty() With {.Name = dr.GetString(0), .Rows = dr.GetString(1), .Reserved = dr.GetString(2), .Data = dr.GetString(3), .IndexSize = dr.GetString(4), .Unused = dr.GetString(5)})
                        dr.NextResult()
                  Loop
                  grdTableInfo.DataSource = tables.OrderBy(Function(o) o.Name)
                  grdTableInfo.DataBind()
            End Using
   End Sub
 
 
As we enumerate through each record, a new TableProperty instance is created and added to the List<TableProperty> collection. The collection is used to bind the information to the GridView control. The next thing to do is to add the code that handles the SelectedIndexChanged event for the DropDownList. Add the following code to handle the event:
 
C#
 
protected void cboDatabase_SelectedIndexChanged(object sender, EventArgs e)
{
Connect();
 
VB.NET
 
Protected Sub cboDatabase_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Connect()
End Sub
 
This will ensure that when the user selects a new database, the information will be retrieved and displayed correctly. The last piece of the puzzle is to add a private property to setup the connection string for the SqlConnection:
 
C#
 
private string Database
{
get
      {
            string database = "master";
            if (cboDatabase.Items.Count > 0)
            {
                  database = cboDatabase.SelectedValue;
            }
            return @"Data Source=MALCOLM-PC\SQLEXPRESS;Initial Catalog=" + database + ";Integrated Security=True";
}
}
 
The above code sets the default database to master because that always exists. Anytime the user changes the database, the connection string will be updated and the data will be reloaded. The hard work is done!
 
 To make the page seem more aesthetically pleasing, add some CSS styles to the table, table row and table cells. Create a new Style Sheet to the project. Name it Info.css and add the following code:
 
body
{
    font-family:Arial;
    font-size:0.8em;
}
 
table
{
    width:600px;
    border:solid 1px #ff0000;
}
 
th
{
    font-weight:bold;
    font-variant:small-caps;
    color:#ff0000;
}
 
td
{
    border:solid 2px #e0e0e0;
    text-align:center;
}
 
In the Default.aspx page, add a link tag to the head section to reference the style sheet:
 
<link href="Info.css" type="text/css" rel="Stylesheet" />
 
 Run the project and a list of databases and each table’s size, row count and other information will be displayed. As I mentioned earlier in this article, I have used this code numerous times to estimate the size and potential growth of tables for companies I have contracted for. Hopefully you can find some use for it as much as I have. The source code for this article can be downloaded from here.

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Malcolm Sheridan is a Microsoft awarded MVP in ASP.NET, a Telerik Insider and a regular presenter at conferences and user groups throughout Australia and New Zealand. Being an ASP.NET guy, his focus is on web technologies and has been for the past 10 years. He loves working with ASP.NET MVC these days and also loves getting his hands dirty with jQuery and JavaScript. He also writes technical articles on ASP.NET for SitePoint and other various websites. Follow him on twitter @malcolmsheridan


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Defcon on Wednesday, March 18, 2009 7:26 AM
your C# class looks weird ;)
Comment posted by Malcolm Sheridan on Wednesday, March 18, 2009 8:09 AM
@Defcon
I thought the C# class looked normal, but weird can be good too :)

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

C# .NET BOOK

C# Book for Building Concepts and Interviews

Tags

JQUERY COOKBOOK

jQuery CookBook