When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, March 29, 2006

Displaying the Sizes of Your SQL Server's Database's Tables, Part 2

By Scott Mitchell


  • Read Part 1

  • In Part 1 we looked at how to use the sp_spaceused and sp_MSforeachtable stored procedures to return information about all of the tables in a database. In this concluding part we'll look at how to take that information and display it in an ASP.NET page, with examples in both ASP.NET 1.x and 2.0.

    Displaying Multiple Result Sets in an ASP.NET 1.x DataGrid
    Using the multiple result sets approach, we can have our ASP.NET page issue a simple query to the database - EXEC sp_MSforeachtable @command1="sp_spaceused '?'" - which will return a result set for each database table. Each result set will contain a single record with the name, rows, reserved, data, index_size, and unused results for the particular table. All of these results can be loaded into a single SqlDataReader object like so:

    'Create & open the connection
    Dim myConnection As New SqlConnection(connectionString)
    myConnection.Open()  'Open the connection
    
    'Specify the SQL statement to execute, and create the command...
    Const SQL as String = "EXEC sp_MSforeachtable @command1=""sp_spaceused '?'"""
    Dim myCommand As New SqlCommand(SQL, myConnection)
    
    'Get the results into a SqlDataReader!
    Dim myReader As SqlDataReader = myCommand.ExecuteReader()
    
    ...
    

    After the last line above executes, myReader contains a result set for each table in the database. If you bind myReader to a data Web control at this time, however, the data Web control will only show the results from the first result set, meaning that your page will only show the size used for the table in the SqlDataReader's first result set.

    In order to get all of the results displayed in the data Web control we need to create some object that holds the results and then iterate through the result sets, adding each result set's single record to the object collection. To facilitate this I created a class called SpaceUsedInfo with properties for holding the name, rows, data size, and index size results from the table. (I omitted the reserved and unused fields in my display, but you could easily add those in if needed.) I also created a class to serve as a strongly-typed collection of SpaceUsedInfo instances named SpaceUsedInfoList. In ASP.NET 2.0 you could use the Generics-based List class to accomplish this without needing to create your own custom strongly-typed collection class.

    Continuing the code listing from above...

    ...
    
    'Define a space delimiter array (used in the Split method below)
    Dim SpaceDelimiter As Char() = {" "}
    
    'Create a new SpaceUsedInfoList instance
    Dim suis As New SpaceUsedInfoList
    
    'For each result set...
    Do
    
      'Read in the records in the result set
      While myReader.Read
         
         'Create a new SpaceUsedInfo object & populate its values based
         'on the values in myReader
         Dim sui As New SpaceUsedInfo
         sui.Name = myReader("name").ToString
         sui.Rows = Convert.ToInt32(myReader("rows"))
         sui.Data = Convert.ToInt32(_
               myReader("data").ToString().Split(SpaceDelimiter)(0))
         sui.Index_Size = Convert.ToInt32(_
               myReader("index_size").ToString().Split(SpaceDelimiter)(0))
    
         'Add the SpaceUsedInfo to the collection
         suis.Add(sui)
      End While
      
      'Loop through each result set... NextResult returns False when we've hit the end
    Loop While myReader.NextResult
    
    'Close the reader and connection
    myReader.Close() : myConnection.Close()
    
    'Finally, bind the results to the data Web control
    DataGridID.DataSource = suis
    DataGridID.DataBind()
    

    Notice that the data returned by sp_spaceused is character data; furthermore, the data and index_size fields (as well as reserved and unused) end with the substring " KB". For my data Web control, however, I want to work with these fields in terms of integers, so that I can format them and have the sorting based on the number (like 504) and not on the string (like "504 KB"). To accomplish this I strip out the number from the returned results using the String's Split() method.

    When using this approach you are responsible for handling sorting the data. This can be accomplished by adding a Sort(sortExpression) method to the SpaceUsedInfoList class and calling it prior to binding the suis object to the data Web control. See the article's download for the code to accomplish this; also review Sorting an Array with Array.Sort for more information.

    Combining Multiple Result Sets Into a Single Result Set Using Temporary Tables
    Rather than placing the burden of combining the multiple result sets generated from calling sp_spaceused on each table in the database on the ASP.NET page, we could shift that burden to a stored procedure. This would greatly simplify the ASP.NET page's logic, since it would merely call a stored procedure and bind those results directly against a data Web control. In fact, as we will see shortly, with ASP.NET 2.0 this can be accomplished declaratively using the SqlDataSource control, meaning that you won't have to write a single line of code! (For more on working with data in ASP.NET 2.0 be sure to read the ongoing Accessing and Updating Data in ASP.NET 2.0 article series.)

    The following stored procedure creates a temporary table (#tblResults), populates it with one record for each result returned by sp_spaceused, picks out the integer results from the reserved, data, index_size, and unused fields, and returns the contents of the temporary table in one result set.

    Continuing the code listing from above...

    CREATE PROCEDURE dbo.TableSpaceUsed
    AS
    
    -- Create the temporary table...
    CREATE TABLE #tblResults
    (
       [name]   nvarchar(20),
       [rows]   int,
       [reserved]   varchar(18),
       [reserved_int]   int default(0),
       [data]   varchar(18),
       [data_int]   int default(0),
       [index_size]   varchar(18),
       [index_size_int]   int default(0),
       [unused]   varchar(18),
       [unused_int]   int default(0)
    )
    
    
    -- Populate the temp table...
    EXEC sp_MSforeachtable @command1=
             "INSERT INTO #tblResults
               ([name],[rows],[reserved],[data],[index_size],[unused])
              EXEC sp_spaceused '?'"
       
    -- Strip out the " KB" portion from the fields
    UPDATE #tblResults SET
       [reserved_int] = CAST(SUBSTRING([reserved], 1, 
                                 CHARINDEX(' ', [reserved])) AS int),
       [data_int] = CAST(SUBSTRING([data], 1, 
                                 CHARINDEX(' ', [data])) AS int),
       [index_size_int] = CAST(SUBSTRING([index_size], 1, 
                                 CHARINDEX(' ', [index_size])) AS int),
       [unused_int] = CAST(SUBSTRING([unused], 1, 
                                 CHARINDEX(' ', [unused])) AS int)
       
    -- Return the results...
    SELECT * FROM #tblResults
    

    This stored procedure will return a single result set. The fields that end in _int are the integer representations of those fields that are returned with KB fields.

    With this stored procedure complete, displaying the results in an ASP.NET 2.0 GridView is as simple as adding the GridView to the page and associating it with a new SqlDataSource control that invokes the TableSpaceUsed stored procedure. You'll need to manually indicate what BoundFields to display in the GridView since the SqlDataSource can't natively determine the schema of the stored procedure's results, but be sure to add in the _int fields (or at least use them as the SortExpression). The following markup is from the ASP.NET 2.0 page included in this article's download and illustrates how to display the results from the stored procedure:

    <asp:GridView ID="GridView1" runat="server" AllowSorting="True"
        AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
    
        <Columns>
            <asp:BoundField DataField="Name" HeaderText="Name" 
                SortExpression="Name" />
    
            <asp:BoundField DataField="Rows" DataFormatString="{0:#,##0}" 
                HeaderText="Rows" HtmlEncode="False"
                SortExpression="Rows">
            </asp:BoundField>
    
            <asp:BoundField DataField="Data_Int" 
                DataFormatString="{0:#,##0} KB" HeaderText="Data Size"
                HtmlEncode="False" SortExpression="Data_Int">
            </asp:BoundField>
    
            <asp:BoundField DataField="Index_Size_Int" 
                DataFormatString="{0:#,##0} KB" HeaderText="Index Size"
                HtmlEncode="False" SortExpression="Index_Size_Int">
            </asp:BoundField>
        </Columns>
    </asp:GridView>
    
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="connectionString"
        SelectCommand="TableSpaceUsed" 
        SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>
    

    Conclusion
    In this article we saw how to display size information for all of the tables in a specified database using the sp_spaceused system stored procedure along with the undocumented sp_MSforeachtable stored procedure. We looked at two ways to use these two stored procedures - calling them directly from the ASP.NET page and using a stored procedure to dump the results into a temporary table, whose data was then returned. The former approach requires a bit more work on the ASP.NET side, but doesn't require creating a stored procedure (which may be ideal if adding a stored procedure involves going to the DBA!). The latter approach makes things on the ASP.NET side much easier, and would be the technique I personally recommend.

    Happy Programming!

  • By Scott Mitchell


    Attachments

  • Download the complete code discussed in this article...



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article