Obscure Problems and Gotchas

I love how I have to search too much to find simple information, one liners, it is just like in College where my college course book would be about 100,000,000,000 pages long and really it says absolutely nothing. The damn book could be condensed down to about 1/10th of it’s size and be more useful. In other words a lot of people out there that write articles about how to do something TALK TOO MUCH just get to the fucking point.

How to get the DataBase size and other Info

EXEC sp_spaceused -- This will give you the size of the database, don't provide arguments (0 arguments)

How to get an Individual Table’s size and other Info

EXEC sp_spaceused 'TableNameHere' -- This will give you the size of a particular table, (1 argument)

How to get All Tables’ sizes and other Info

EXEC sp_MSforeachtable @command1 = 'EXEC sp_spaceused ''?''' -- This is for all tables, the '?' serves as a wild card

The idea of the above is that the SP “sp_MSforeachtable” will execute another SP for all tables in the DataBase. The downside is that it will also return each result in its own result set. In .Net terms that translates into a DataSet, so I took that point and wrote a powershell script to iterate through all that stuff and dump all of the rows into a single table and then into a CSV file…

How to export this data to a single data result and CSV

[string]$query = "EXEC sp_MSforeachtable @command1 = 'EXEC sp_spaceused ''?''' ";

#You can use which ever method you prefer
#$connString = "Data Source=localhost,port;Initial Catalog=DataBaseNameHere;Integrated Security=True"
$connString = "Data Source=localhost,port;Initial Catalog=DataBaseNameHere; uID=userName; password=1234"

#setup your connection object
$connection = new-object system.data.sqlclient.sqlconnection($connString);

#get the adapter object
$adapter = new-object system.data.sqlclient.sqldataadapter($query, $connection);

#initialize the DataSet object
$set = new-object system.data.dataset;

#fill the DataSet object
$adapter.Fill($set);

#initialize the DataTable object
$table = new-object system.data.datatable;

#Don't bother with the next operation if the DataSet is empty
if($set.Tables.Count -gt 0)
{
 #declare a DataRow object
        [System.Data.DataRow]$dr = $null;
 
        #Get the structure of the table once so it doesn't have to be done manually
 $table = $set.Tables[0].Clone();
 
        #for each DataTable in the DataSet transfer each row over
 foreach($dt in $set.Tables)
 {
  #Create a new row
                $dr = $table.NewRow();
  
                #For my needs I needed to specify the column names, but you can make this more generic by
                #iterating through each column by index. There is only a single row per table, so the 0 
                #is hard coded on purpose
  $dr["name"] = $dt.Rows[0]["name"];
  $dr["rows"] = $dt.Rows[0]["rows"];
  $dr["reserved"] = $dt.Rows[0]["reserved"];
  $dr["data"] = $dt.Rows[0]["data"];
  $dr["index_size"] = $dt.Rows[0]["index_size"];
  $dr["unused"] = $dt.Rows[0]["unused"];  

                #Finally add the new row to our table
  $table.Rows.Add($dr);
 }
}

#This line will auto format a table
$table | format-table -AutoSize;

#This line will export the table results to a CSV file
$table | Export-Csv ".TableSizes.csv";

BAM Straight to the point, that’s how you do it!