Retrieve table names

Question

I'm trying to retrieve the names of the table from the local database I'm using.

This the code I've tried but it never goes through the foreach loop:

public void GetColumnNames()
    {
        SqlConnection con;
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet ds = new DataSet();

        con = new SqlConnection(Properties.Settings.Default.AlhusainSoundDBConnectionString);

        List<string> colns = new List<string>();

            try
        {
            con.Open();
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }

        foreach (DataTable dt in ds.Tables)
            {
                colns.Add(dt.TableName);
                Console.WriteLine(dt.TableName);
            }
    }

So could anyone please suggest me how to do that correctly

Regards

Where did you fill your `ds` exactly? I don't see _any_ code to do that. — Soner Gönül, Nov 18 '15 at 15:33
There's no code here that actually selects data from the database. `foreach` won't iterate over an empty collection. — David, Nov 18 '15 at 15:35

score 1 · Answer 1 · answered Nov 18 '15 at 15:35

1

To get table names you need to use INFORMATION_SCHEMA

USE <your_database_name>
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES

answered Nov 18 '15 at 15:35

sumeet kumar

2,628
1
16
24

snow_FFFFFF · Answer 2 · 2015-11-18T16:04:23.323

1

You haven't done anything except open a connection to the database. Your dataset has not been populated with any data. My approach would be to use a SqlCommand object to execute the following SQL Statement and populate a SqlDataReader

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

So, the C# code might look something like this:

string sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";

using (SqlConnection con = new SqlConnection(Properties.Settings.Default.AlhusainSoundDBConnectionString))
using (SqlCommand cmd = new SqlCommand(sql, con))
{
    con.Open();
    using (SqlDataReader dr = cmd.ExecuteReader())
    {
        while (dr.Read())
        {
            // do something with each table
            string tableName= dr["TABLE_NAME"].ToString();
            // OR
            // string tableName = dr[0].ToString();
            // OR
            // string tableName = dr.GetString(0);
        }
    }
}

edited Nov 18 '15 at 16:04

answered Nov 18 '15 at 15:35

snow_FFFFFF

3,235
17
29

Thanks a lot, now how can I get the table names from the reader? @snow_FFFFFF – Husain Alhamali Nov 18 '15 at 15:46
@AliAlhamaly During the `while` you get the data row by row and you just need to access the datareader as you can see here: http://stackoverflow.com/questions/4018114/read-data-from-sqldatareader – DatRid Nov 18 '15 at 15:59
updated the answer with some detail on getting values from the reader – snow_FFFFFF Nov 18 '15 at 16:04

Retrieve table names

2 Answers2