我可以在C＃应用程序中获取SQL Server数据库的所有表的名称吗？

I want to get name of all table of SQL Server database in my C# application. Is It possible? Plz tell me Solution.

我想在我的C＃应用程序中获取所有SQL Server数据库表的名称。可能吗？ Plz告诉我解决方案。

9 个解决方案

#1

It is as simple as this:

这很简单：

DataTable t = _conn.GetSchema("Tables");

where _conn is a SqlConnection object that has already been connected to the correct database.

其中_conn是已连接到正确数据库的SqlConnection对象。

#2

Just another solution:

另一个解决方案：

 public IList<string> ListTables()
    {
        List<string> tables = new List<string>();
        DataTable dt = _connection.GetSchema("Tables");
        foreach (DataRow row in dt.Rows)
        {
            string tablename = (string)row[2];
            tables.Add(tablename);
        }
        return tables;
    }

#3

Run a sql command for:

运行sql命令：

SELECT name FROM sysobjects WHERE xtype = 'U'

#4

See How to get a list of SQL Server databases for one way:

请参阅如何以一种方式获取SQL Server数据库列表：

System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection("server=192.168.0.1;uid=sa;pwd=1234");
SqlCon.Open();

System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";

System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();

while(SqlDR.Read())
{
MessageBox.Show(SqlDR.GetString(0));
}

#5

If you want to get all table names from a database you can do something like this ;

如果你想从数据库中获取所有表名，你可以做这样的事情;

string[] GetAllTables(SqlConnection connection)
{
  List<string> result = new List<string>();
  SqlCommand cmd = new SqlCommand("SELECT name FROM sys.Tables", connection);
  System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
  while(reader.Read())
   result.Add(reader["name"].ToString());
  return result.ToArray();
}

Get all databases using the other response and create a connection to each and use function "GetAllTables" to get all table names from that db.

使用其他响应获取所有数据库并创建与每个数据库的连接，并使用函数“GetAllTables”从该数据库中获取所有表名。

#6

Another way but worth mentioning: The API contained in Microsoft.SqlServer.Smo.dll makes it very to access database:

另一种值得一提的方法：Microsoft.SqlServer.Smo.dll中包含的API使它非常适合访问数据库：

private IEnumerable<string> getAllTables()
{
  var sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
  var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
  var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
  var database = server.Databases[databaseName];
  foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
  {
    yield return table.Name;
  }
}

The coolest thing is that Microsoft.SqlServer.Management.Smo.Table object allows you to perform all kinds of operations, like changing schema, scripting, etc...

最酷的是Microsoft.SqlServer.Management.Smo.Table对象允许您执行各种操作，如更改架构，脚本等...

#7

I am using this ExtensionMethod for SqlConnection:

我正在使用这个ExtensionMethod for SqlConnection：

public static List<string> GetTableNames(this SqlConnection connection)
{
    using(SqlConnection conn = connection)
    {
        if(conn.State == ConnectionState.Open)
        {
            return conn.GetSchema("Tables").AsEnumerable().Select(s => s[2].ToString()).ToList();
        }            
    }
    //Add some error-handling instead !
    return new List<string>();        
}

#8

My version of yonexbat answer

我的yonexbat版本答案

public System.Collections.Generic.Dictionary<string, string> GetAllTables(System.Data.SqlClient.SqlConnection _connection)
{
    if (_connection.State == System.Data.ConnectionState.Closed)
        _connection.Open();
    System.Data.DataTable dt = _connection.GetSchema("Tables");
    System.Collections.Generic.Dictionary<string, string> tables = new System.Collections.Generic.Dictionary<string, string>();
    foreach (System.Data.DataRow row in dt.Rows)
    {
        if (row[3].ToString().Equals("BASE TABLE", StringComparison.OrdinalIgnoreCase)) //ignore views
        {
            string tableName = row[2].ToString();
            string schema = row[1].ToString();
            tables.Add(tableName, schema);
        }
    }
    _connection.Close();
    return tables;
}

#9

Thanks to Slugster for his answer. Here is an expanded explanation of how to view a list of the tables in an database.

感谢Slugster的回答。以下是如何查看数据库中表的列表的扩展说明。

in an asp.net form add the following:

在asp.net表单中添加以下内容：

<div>
    <asp:Button ID="GridViewTableListButton" runat="server" Text="List all Tables on server" 
        onclick="GridViewTableListButton_Click"  />
    <asp:GridView ID="GridViewTableList" runat="server">
    </asp:GridView>
</div>

then in C# code behind add the following function:

然后在C＃代码后面添加以下函数：

protected void GridViewTableListButton_Click(object sender, EventArgs e)
{
    objConn.Open();
    DataTable t = objConn.GetSchema("Tables");
    GridViewTableList.DataSource = t;
    GridViewTableList.DataBind();
    objConn.Close();
}

not forgetting to add

不要忘记添加

using System.Data;

and

和

SqlConnection objConn = new SqlConnection();

at the top of you page / inside your parent class.

在您页面的顶部/您的父类内部。

With inside your Page_Load:

在你的Page_Load里面：

objConn.ConnectionString = ConfigurationManager.ConnectionStrings[connString].ConnectionString;

connString is a class file (called connectionClass.cs) that is stored in the App_Code folder

connString是一个存储在App_Code文件夹中的类文件（称为connectionClass.cs）

public class connectionClass
{
.....
    public string connClass()
    {
        connString = "LocalSqlServer"; // LOCAL home PC Version
    }
}

then finally in web.config

然后终于在web.config中

<add name="LocalSqlServer" connectionString="Data Source=MyPCsName\SQLEXPRESS;Initial Catalog=databasename;Integrated Security=True" providerName="System.Data.SqlClient"/>

for example

例如

#1