Get table list in DB using C# and ADO.NET

How to get list of all tables from SQL Server DB in C#?

In C# we can get the list of all table names from the SQL Server DB easily, the following code will help us in getting the table names and it also load all the table data to the dataset.


Option#1

If we use the following code we need not know all the table names to get the data from the DB.

try
{
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
object[] objArrRestrict;
objArrRestrict = new object[] {null, null, null, "TABLE"};
schemaTbl = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,objArrRestrict);
foreach (DataRow row in schemaTbl.Rows)
{
cmd.CommandText = "select * from ["+row["TABLE_NAME"]+"]";
da.SelectCommand = cmd;
da.Fill(ds, row["TABLE_NAME"].ToString());
}
}
catch (SqlException ex)
{
MessageBox.Show("Error" + ex.Message);
}

The above code will load the dataset “ds” with the data in all the tables in DB given in the connection string.

The method "GetOleDbSchemaTable" does the magic here, it helps us to retrieve all the tables names.

Option#2

The below code snipper will also help us to retrieve set of table names.

try
{
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "select * from sysobjects where type='U'";
ds = cmd.ExecuteQuery();
}
catch (SqlException ex)
{
MessageBox.Show("Error" + ex.Message);
}

This code makes use of the SQL server system table to fetch all the user tables.

Note
Option#1 is used to retrieve Schema information of any OLEDB data source. Option #2 is restricted to SQL Server because othee RDBMS don't have system table which lists the tables. Also note that SqlConnection object don't have the method "GetOleDbSchemaTable" to retrieve schema information.