lowedown · September 20, 2018 14:14
diff --git a/DatabaseStats.aspx b/DatabaseStats.aspx
 <%@ Page Language="C#" AutoEventWireup="true" %>

 <%@ Import Namespace="System.Data.SqlClient" %>
 <%@ Import Namespace="System.Text" %>
 <%@ Import Namespace="System.Configuration" %>

 <!DOCTYPE html>

 <html xmlns="http://www.w3.org/1999/xhtml">
 <head runat="server">
    <title></title>
 </head>
 <body>
    <form id="form1" runat="server">
        <div>
            <h1>Database stats</h1>
            
            <asp:Button runat="server" ID="getStatsBUtton" OnClick="getStatsBUtton_OnClick" Text="Get Stats" />
            
            <h2>Master</h2>
            <asp:GridView ID="masterStats" runat="server">
            </asp:GridView>
            
            <h2>Web</h2>
            <asp:GridView ID="webStats" runat="server">
            </asp:GridView>
            
            <h2>Web2</h2>
            <asp:GridView ID="web2Stats" runat="server">
            </asp:GridView>
        </div>
        
        <script language="C#" runat="server">  
            protected void getStatsBUtton_OnClick(object sender, EventArgs e)
            {
                GetStatsForDb("master", masterStats);
                GetStatsForDb("web", webStats);
                GetStatsForDb("web2", web2Stats);
            }

            private void GetStatsForDb(string connectionString, GridView gridView)
            {
                var query = @" SELECT 
            t.NAME AS TableName,
                s.Name AS SchemaName,
                p.rows AS RowCounts,
                SUM(a.total_pages) * 8 AS TotalSpaceKB,
                CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
                SUM(a.used_pages) *8 AS UsedSpaceKB,
                CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
            (SUM(a.total_pages) - SUM(a.used_pages)) *8 AS UnusedSpaceKB,
                CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
            FROM
            sys.tables t
            INNER JOIN
            sys.indexes i ON t.OBJECT_ID = i.object_id
            INNER JOIN
            sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
            INNER JOIN
            sys.allocation_units a ON p.partition_id = a.container_id
            LEFT OUTER JOIN
            sys.schemas s ON t.schema_id = s.schema_id
            WHERE
            t.NAME NOT LIKE 'dt%'
            AND t.is_ms_shipped = 0
            AND i.OBJECT_ID > 255
            GROUP BY
            t.Name, s.Name, p.Rows
                ORDER BY
            t.Name";

                

                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionString].ToString()))
                {
                    //
                    // Open the SqlConnection.
                    //
                    con.Open();
                    //
                    // The following code uses an SqlCommand based on the SqlConnection.
                    //
                    using (SqlCommand command = new SqlCommand(query, con))
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        gridView.DataSource = reader;
                        gridView.DataBind(); 
                    }
                }

                
            }
        </script>

 </form>
    </body>
    </html>
	<%@ Page Language="C#" AutoEventWireup="true" %>

	<%@ Import Namespace="System.Data.SqlClient" %>
	<%@ Import Namespace="System.Text" %>
	<%@ Import Namespace="System.Configuration" %>

	<!DOCTYPE html>

	<html xmlns="http://www.w3.org/1999/xhtml">
	<head runat="server">
	<title></title>
	</head>
	<body>
	<form id="form1" runat="server">
	<div>
	<h1>Database stats</h1>

	<asp:Button runat="server" ID="getStatsBUtton" OnClick="getStatsBUtton_OnClick" Text="Get Stats" />

	<h2>Master</h2>
	<asp:GridView ID="masterStats" runat="server">
	</asp:GridView>

	<h2>Web</h2>
	<asp:GridView ID="webStats" runat="server">
	</asp:GridView>

	<h2>Web2</h2>
	<asp:GridView ID="web2Stats" runat="server">
	</asp:GridView>
	</div>

	<script language="C#" runat="server">
	protected void getStatsBUtton_OnClick(object sender, EventArgs e)
	{
	GetStatsForDb("master", masterStats);
	GetStatsForDb("web", webStats);
	GetStatsForDb("web2", web2Stats);
	}

	private void GetStatsForDb(string connectionString, GridView gridView)
	{
	var query = @" SELECT
	t.NAME AS TableName,
	s.Name AS SchemaName,
	p.rows AS RowCounts,
	SUM(a.total_pages) * 8 AS TotalSpaceKB,
	CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
	SUM(a.used_pages) *8 AS UsedSpaceKB,
	CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
	(SUM(a.total_pages) - SUM(a.used_pages)) *8 AS UnusedSpaceKB,
	CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
	FROM
	sys.tables t
	INNER JOIN
	sys.indexes i ON t.OBJECT_ID = i.object_id
	INNER JOIN
	sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
	INNER JOIN
	sys.allocation_units a ON p.partition_id = a.container_id
	LEFT OUTER JOIN
	sys.schemas s ON t.schema_id = s.schema_id
	WHERE
	t.NAME NOT LIKE 'dt%'
	AND t.is_ms_shipped = 0
	AND i.OBJECT_ID > 255
	GROUP BY
	t.Name, s.Name, p.Rows
	ORDER BY
	t.Name";



	using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionString].ToString()))
	{
	//
	// Open the SqlConnection.
	//
	con.Open();
	//
	// The following code uses an SqlCommand based on the SqlConnection.
	//
	using (SqlCommand command = new SqlCommand(query, con))
	using (SqlDataReader reader = command.ExecuteReader())
	{
	gridView.DataSource = reader;
	gridView.DataBind();
	}
	}


	}
	</script>

	</form>
	</body>
	</html>