This article shows how we can determine the size of an entire database using C# and the size of each and every table in the database using a single SQL command. To explain this, it is divided into three sections,
- Getting Size of one Table in the Database using single SQL Command
- Getting Size of each Table in the Database using single SQL Command
- Getting Size of the entire Database using C#
Getting Size of one Table in the Database using single SQL Command
To get the table size in SQL Server, we need to use the system stored procedure sp_spaceused. If we Table Name as an argument, it gives the disk space used by the table and some other information like: Number of rows existing in the table, Total amount of reserved space for Table, Total amount of space reserved for the table but not yet used and Total amount of space used by indexes in Table.
Example
For the ADDRESS table in our database, if we run,
sp_spaceused 'TADDRS'
It will give the following result,
Getting Size of each Table in the Database using single SQL Command
We have seen how we can determine the size of one table. Now, suppose we want to determine the size of each table in the entire database. We could find the size of any table using this command just by changing the Table name in the parameter. Is that right? But would it not be much better if we have a one-line SQL command that gives the size of each table? Right?
Fortunately, SQL Server provides a way to do this. A stored procedure "sp_MSforeachtable" could do easily for us!
The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. You can find more details of "sp_MSforeachtable" in this link.
sp_MSforeachtable is an undocumented stored procedure that is not listed in MSDN Books Online and can be used to run a query against each table in the database. In short, you can use this as,
EXEC sp_MSforeachtable @command1="command to run"
In the "command to run" put a "?" , where you want the table name to be inserted. For example, to run the sp_spaceused stored procedure for each table in the database, we'd use,
It will give the size of each table (including other details) like,
Getting the Size of the entire Database
Now we want to get the total size used by a database i.e. the additional space used by each table in the database. We have seen in the previous section how we can get the size of each table in the database. Here is sample code in C# that can be used to calculate the size of the entire database.
In this, we are executing the same command what we discussed in the above section and are querying the database using simple ADO.Net. We get the result in a DataSet and then iterate each table to get its size. The table size is stored in the "data" column of each table (see the above Picture-1). We just add the "data" column value of each table to get the size of the entire database. Sample code is also attached with this article.
class MemorySizeCalculator
{
public void GetDbSize()
{
int sum = 0;
// Database Connection String
string sConnectionString = "Server = .; Integrated Security = true; database = HKS";
// SQL Command [Same command discussed in section-B of this article]
string sSqlquery = "EXEC sp_MSforeachtable @command1=\"EXEC sp_spaceused '?'\" ";
DataSet oDataSet = new DataSet();
// Executing SQL Command using ADO.Net
using (SqlConnection oConn = new SqlConnection(sConnectionString))
{
oConn.Open();
using (SqlCommand oCmdGetData = new SqlCommand(sSqlquery, oConn))
{
oCmdGetData.ExecuteNonQuery();
SqlDataAdapter executeAdapter = new SqlDataAdapter(oCmdGetData);
executeAdapter.Fill(oDataSet);
}
oConn.Close();
}
// Iterating each table
for (int i = 0; i < oDataSet.Tables.Count; i++)
{
// We want to add only "data" column value of each table
sum = sum + Convert.ToInt32(oDataSet.Tables[i].Rows[0]["data"].ToString().Replace
("KB", "").Trim());
}
Console.WriteLine("Total size of the database is : " + sum + " KB");
}
}
HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
