M4V3R1CK M4V3R1CK - 5 months ago 12
SQL Question

Retrieve List of Tables from Specific Database on Server C#

Looking for some C# examples that can retrieve the tables' names from a specific database on a server. I already have a valid connection string, just looking for the right way of adding the names of each table into List for later retrieval and manipulation.

Answer

System.Data.SqlClient has what you need without a formal query on sys.Tables (though that's what it's using in the background). Use the GetSchema() method on the SqlConnection object and designate that you want the "Tables" and it will send you a DataTable object back with a row for each table. It sends back database name, table schema name, table name, and table type in each row (in that column order). The code would look like this:

public static List<string> GetTables(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        DataTable schema = connection.GetSchema("Tables");
        List<string> TableNames = new List<string>();
        foreach (DataRow row in schema.Rows)
        {
            TableNames.Add(row[2].ToString());
        }
        return TableNames;
    }
}