To get the information about the database and the tables I have used the Stored Procedure called "sp_spaceused". I have given few lines about the sp_spaceused.Some times we may have to know the imformation about the database for the backup's. If we make this application as a console before taking the backup, we can check the information about the database that we are working on.
sp_spaceused:
Displays the number of rows, disk space reserved, and disk space used by a table,
indexed view, or Service Broker queue in the current database, or displays the
disk space reserved and used by the whole database.
Syntax:
sp_spaceused [[ @objname = ] 'objname' ]
[,[ @updateusage = ] 'updateusage' ]
Arguments:
[ @objname=] 'objname'
Is the qualified or nonqualified name of the table, indexed view, or queue for
which space usage information is requested. Quotation marks are required only
if a qualified object name is specified. If a fully qualified object name (including
a database name) is provided, the database name must be the name of the current
database.
If objname is not specified, results are returned for the whole database.
objname is nvarchar(776), with a default of NULL.
[ @updateusage=] 'updateusage'
Indicates DBCC UPDATEUSAGE should be run to update space usage information. When
objname is not specified, the statement is run on the whole database; otherwise,
the statement is run on objname. Values can be true or false. updateusage is
varchar(5), with a default of false.
Returns:
0 (success) or 1 (failure)
The code as follows here
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SizeOfDB.aspx.cs"
Inherits="SizeOfDB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Displays the number of rows, disk space reserved, and disk space used by a table,
indexed view, or Service Broker queue in the current database, or displays
the disk
space reserved and used by the whole database. </title>
<style type="text/css">
.watermarkOn
{
color: #CCCCCC;
font: Verdana normal 10px;
}
</style>
<script type="text/javascript" src="http://code.jquery.com/jquery-1.4.1.js"></script>
<script type="text/javascript">
$(document).ready(function () {
var theText = $('#hdnSelectedIndex').val();
if (theText == null || theText == undefined || theText == "")
{
$("#ddlTablesList option:contains('Select Any Table')").attr('selected',
'selected');
$('#ddlTablesList')[0][0].className = 'watermarkOn'; //Here adding a watermark class
to the first item of the dropdownlist
}
else {
$("#ddlTablesList option:contains(" + theText + ")").attr('selected',
'selected');
$('#ddlTablesList').removeClass('watermarkOn');
}
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2>
Displaying updated space information about a database
</h2>
<asp:GridView ID="gv1" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None"
BorderWidth="1px" CellPadding="3" CellSpacing="2">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<br />
<asp:DropDownList ID="ddlTablesList" runat="server" OnSelectedIndexChanged="ddlTablesList_SelectedIndexChanged"
AutoPostBack="true" EnableViewState="true">
</asp:DropDownList>
<br />
<h2>
Displaying disk space information about a table
</h2>
<asp:GridView ID="gv2" runat="server" BackColor="White" BorderColor="#999999" BorderWidth="1px"
CellPadding="3" GridLines="Vertical" BorderStyle="None">
<AlternatingRowStyle BackColor="#DCDCDC" />
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<HeaderStyle BackColor="#F6358A" Font-Bold="True" ForeColor="#FFFFFF" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<SelectedRowStyle BackColor="#008A8C" ForeColor="White" Font-Bold="True" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#0000A9" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#000065" />
</asp:GridView>
<br />
<asp:HiddenField ID="hdnSelectedIndex" runat="server" />
</div>
</form>
</body>
</html>
In the Code behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
public partial class SizeOfDB : System.Web.UI.Page
{
String connectionString = ConfigurationManager.ConnectionStrings["CON"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Calculate_DB_DiskSpaceUsed(connectionString);
FillDropDownList(connectionString);
}
}
/// <summary>
/// Calculate_DB_DiskSpaceUsed(This method is used to calculate the database update
usage)
/// </summary>
/// <param name="con"></param>
void Calculate_DB_DiskSpaceUsed(string con)
{
using (SqlConnection sqlcon = new SqlConnection(con))
{
SqlCommand cmd = new SqlCommand("sp_spaceused", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@updateusage", Boolean.TrueString));
sqlcon.Open();
SqlDataReader dr = cmd.ExecuteReader(); DataTable dt = new DataTable();
if (dr.HasRows)
{
dt.Columns.Add("Database_Name", typeof(string));
dt.Columns.Add("Database_Size", typeof(string));
dt.Columns.Add("Unallocated Space", typeof(string));
while (dr.Read())
{
dt.Rows.Add(dr["database_name"].ToString(), dr["database_size"].ToString(), dr["unallocated space"].ToString());
}
}
gv1.DataSource = dt;
gv1.DataBind();
}
}
/// <summary>
/// FillDropDownList(The method is used to fill the dropdownlist
/// </summary>
/// <param name="con"></param>
void FillDropDownList(String con)
{
ddlTablesList.DataBind();
using (SqlConnection sqlcon = new SqlConnection(con))
{
String Query = "SELECT name,OBJECT_ID from sys.tables";
SqlCommand cmd = new SqlCommand(Query, sqlcon);
cmd.CommandType = CommandType.Text;
sqlcon.Open();
SqlDataReader dr = cmd.ExecuteReader();
DataTable dtTableLists = new DataTable();
if (dr.HasRows)
{
dtTableLists.Columns.Add("Name", typeof(string));
dtTableLists.Columns.Add("Object Id", typeof(string));
while (dr.Read())
{
dtTableLists.Rows.Add(dr["name"].ToString(), dr["object_id"].ToString());
}
ddlTablesList.DataSource = dtTableLists;
ddlTablesList.DataTextField = dtTableLists.Columns["Name"].ToString();
ddlTablesList.DataValueField = dtTableLists.Columns["Object Id"].ToString();
ddlTablesList.DataBind();
ddlTablesList.Items.Insert(0, new ListItem("Select Any Table ", "-1", true));
}
dr.Dispose();
}
}
protected void ddlTablesList_SelectedIndexChanged(object sender, EventArgs e)
{
String selectedText = ddlTablesList.SelectedItem.Text;
hdnSelectedIndex.Value = selectedText;
int ddlSelectedIndex = ddlTablesList.SelectedIndex;
if (ddlSelectedIndex != 0)
{
using (SqlConnection sqlcon = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("sp_spaceused");
cmd.Connection = sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
sqlcon.Open();
cmd.Parameters.Add(new SqlParameter("@objname", selectedText));
SqlDataReader dr = cmd.ExecuteReader();
DataTable dtTableDetails = new DataTable();
if (dr.HasRows)
{
dtTableDetails.Columns.Add("Name", typeof(string));
dtTableDetails.Columns.Add("Rows", typeof(string));
dtTableDetails.Columns.Add("Reserved", typeof(string));
dtTableDetails.Columns.Add("Data", typeof(string));
dtTableDetails.Columns.Add("Index Size", typeof(string));
dtTableDetails.Columns.Add("Unused", typeof(string));
while (dr.Read())
{
dtTableDetails.Rows.Add(dr["name"].ToString(), dr["rows"].ToString(), dr["Reserved"].ToString(), dr["data"].ToString(), dr["index_size"].ToString(), dr["unused"].ToString());
}
gv2.DataSource = dtTableDetails;
gv2.DataBind();
} dr.Dispose();
}
}
else
{
Response.Write("<script>alert('Please select any table')</script>");
}
}
}
Note:
You can also run sp_spaceused without any parameters to display information about
the whole database. Replace the names in bold with your own:
USE NorthWind
GO
EXEC sp_spaceused
GO
Results
First Recordset:
database_name: Name of the current database
database_size: Size of the current database in megabytes. database_size includes
both data and log files
unallocated space: Space in the database that has not been reserved for database
objects
Second Recordset:
reserved: Total amount of space allocated by objects in the database
data: Total amount of space used by data
index_size: Total amount of space used by indexes
unused: Total amount of space reserved for objects in the database, but not yet
used
The source code is available here.