[ASP.NET]利用Stored Procedures sp_MSforeachtable來取得DB所有Table的筆數

2009-10-20

13745
0
ASP.NET

利用Stored Procedures sp_MSforeachtable來取得DB所有Table的筆數

原來有一個還不錯用的Stored Procedures → sp_MSforeachtable

小弟就利用這個sp來把某個DB裡所有的Table的筆數顯示在GridView上

asp.net(c#)
sp_MSforeachtable.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="sp_MSforeachtable.aspx.cs"
    Inherits="sp_MSforeachtable" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>sp_MSforeachtable</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

sp_MSforeachtable.aspx.cs

using System;
using System.Data.SqlClient;
using System.Data;

public partial class sp_MSforeachtable : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connStr = "Data Source=localhost;Initial Catalog=msdb;User ID=sa;password=sasa;";
        SqlConnection db = new SqlConnection(connStr);

        SqlCommand cmd = new SqlCommand("sp_MSforeachtable", db);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@command1", SqlDbType.NVarChar, 2000);
        cmd.Parameters["@command1"].Value = "select \"?\" as TableName, count(*) as TotalRows from ?";

        try
        {
            db.Open();
            SqlDataReader rd = cmd.ExecuteReader();

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("TableName", typeof(string)));
            dt.Columns.Add(new DataColumn("TotalRows", typeof(int)));

            do
            {
                while (rd.Read())
                {
                    DataRow dr = dt.NewRow();
                    dr["TableName"] = rd.GetValue(0);
                    dr["TotalRows"] = rd.GetValue(1);
                    dt.Rows.Add(dr);
                }
            }
            while (rd.NextResult());

            dt.DefaultView.Sort = "TotalRows desc";//依筆數由大到小排序
            this.GridView1.DataSource = dt;
            this.GridView1.DataBind();

            rd.Close();
        }
        catch (Exception ex)
        {
            throw ex.GetBaseException();
        }
        finally
        {
            db.Close();
        }
    }
}

執行結果：

參考網址：
http://smehrozalam.wordpress.com/2009/05/26/t-sql-sp_msforeachdb-and-sp_msforeachtable-undocumented-but-very-powerful-stored-procedures/
http://blog.miniasp.com/post/2008/12/How-to-get-Stored-Procedure-return-value-using-ADONET.aspx
http://www.cnblogs.com/9999/archive/2009/04/22/1440959.html

回首頁