Getting all Table name in a database with c#

Question

I am using VS2010 , .Net 4.0, MS SQL SERVER 2008.

I thought I know how to get all table names from a Database. But I was wrong. With table names, I get View names as well as. But when i do it for Views, I just only get Views. I have no idea why this is happening.

Here is my code, what I have tried:

    public DataTable getAllTables(string serverName, string dbName, string authenticationType, string Login, string pass)
    {
        using (SqlConnection sqlConn = Return_Conn(serverName, dbName, authenticationType, Login, pass))
        {
            sqlConn.Open();
            DataTable dt = sqlConn.GetSchema("Tables");
            DataTable dt1 = new DataTable();

            string[] column = { "TABLE_NAME" };
            dt1 = dt.DefaultView.ToTable("dd", false, column);

            sqlConn.Close();
            return dt1;
        }
    }

    public DataTable getAllViews(string serverName, string dbName, string authenticationType, string Login, string pass)
    {
        using (SqlConnection sqlConn = Return_Conn(serverName, dbName, authenticationType, Login, pass))
        {
            sqlConn.Open();
            DataTable dt = sqlConn.GetSchema("Views");
            DataTable dt1 = new DataTable();

            string[] column = { "TABLE_NAME" };
            dt1 = dt.DefaultView.ToTable("dd", false, column);

            sqlConn.Close();
            return dt1;
        }
    }

in DataTable dt = sqlConn.GetSchema("Tables"); this line, dt contains all tables along with views. and in dt, table_type for Views are showing "View" and for table, showing "Base Table".

but in DataTable dt = sqlConn.GetSchema("Views"); this line, dt contains only views.

What is wrong here?How could I solve this?

Answer 1

If you would like to only get Tables, then you will need to restrict schema. For Table there are four restrictions supported : (1)Catalog, (2)Owner, (3)Table, and (4)TableType.

GetSchema (string collectionName, string[] restrictionValues);

So you can write as:

var tables = sqlConn.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" });

Answer 2

I'd run a query like this rather than GetSchema:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'