ASP.NET利用SQL指令取得MS SQL資料庫裡所有Table的名稱

2008-05-24

23317
0
ASP.NET

這個問題是前陣子有人問到的問題,,最近比較忙..所以現在才貼出來...
只要下下列sql語法就可以找出ms sql裡所有資料表的名稱,包含系統用的都找的出來...

這個問題是前陣子有人問到的問題,,最近比較忙..所以現在才貼出來...

只要下下列sql語法就可以找出ms sql裡所有資料表的名稱,包含系統用的都找的出來...

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_SCHEMA = 'dbo')

這個指令只針對ms sql喔..不同資料庫,有不同的語法...

小弟也寫一個範例,來呈現資料...

asp.net(c#)

GetDBTable.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GetDBTable.aspx.cs" Inherits="GetDBTable" %> <!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>GetDBTable</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="選擇資料表"></asp:Label> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="TABLE_NAME" DataValueField="TABLE_NAME" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_SCHEMA = 'dbo')"> </asp:SqlDataSource> </div> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </form> </body> </html>

GetDBTable.aspx.cs

using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; public partial class GetDBTable : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) { string queryString = string.Format("SELECT * FROM [{0}] ", this.DropDownList1.SelectedValue); SqlCommand cmd = new SqlCommand(queryString, conn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); this.GridView1.DataSource = dt; this.GridView1.DataBind(); } } }

執行結果：

參考網址：

http://www.dotnet-friends.com/fastcode/database/adonet/csharp/dcf72599-a6c6-42e2-a21a-c4d254a86189.aspx

回首頁