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.
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!
Was this article worth reading? Share it with fellow developers too. Thanks!
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