How To Get SQL Server Database Size In C#

EXEC sp_MSforeachtable @command1="command to run."

In the "command to run," put a "?" where you want the table name 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,

Picture 1 - Query Result

Getting the Size of the entire Database

We have seen in the previous section how we can get the size of each Table in the Database. Now we want to get the total size a database uses, i.e., the additional space used by each Table in the Database. Here is a sample code in C# that can calculate the entire database's size.

In this, we are executing the same command 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 add each Table's "data" column value to get the entire Database's size. The sample code is also attached to 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");  
 }  
}  

Conclusion

This article shows how we can determine the size of an entire database using C# and the size of every table in the database using a single SQL command.