When writing C# code, if I can manage to get a SqlConnection, is there any way to perform a query to get the size of the Database?
I searched the internet, seems "sp_spaceused" can be used, but it is not a table, it is a procedure.
Can I query a procedure?
The following code (taken from a console app testharness) calls the sp_spaceused procedure using ADO.NET and then iterates the resulting datareader to get the db size.
This works, but I can't say that there isn't a more efficient or direct way of achieving what you want.
One alternative implementation would be to wrap the sp_spaceused procedure in your own procedure that gives the exact data your need as a scalar return value.
class Program
{
static void Main(string[] args)
{
string strCount;
SqlConnection Conn = new SqlConnection
("Data Source=ServerName;integrated " +
"Security=sspi;initial catalog=TestingDB;");
SqlCommand testCMD = new SqlCommand
("sp_spaceused", Conn);
testCMD.CommandType = CommandType.StoredProcedure;
Conn.Open();
SqlDataReader reader = testCMD.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("Name: " + reader["database_name"]);
Console.WriteLine("Size: " + reader["database_size"]);
}
}
Console.ReadLine();
}
}
for a single table you can use: so to do sp_spaceused using c# for a single table this will work:
public static void GetTableSpaceUsed(string tableName)
{
SqlConnection Conn = new SqlConnection(SqlHelper.GetConnection());
SqlCommand spaceused = new SqlCommand("sp_spaceused", Conn);
spaceused.CommandType = CommandType.StoredProcedure;
spaceused.Parameters.AddWithValue("@objname", tableName);
Conn.Open();
SqlDataReader reader = spaceused.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("Name: " + reader["name"]);
Console.WriteLine("Rows: " + reader["rows"]);
Console.WriteLine("Reserved: " + reader["reserved"]);
Console.WriteLine("Data: " + reader["data"]);
Console.WriteLine("Index size: " + reader["index_size"]);
}
}
}
