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?

possible duplicate query [How to get all tables of a MSSQL-Database?](http://stackoverflow.com/questions/10517991/how-to-get-all-tables-of-a-mssql-database) — spajce, Dec 14 '12 at 11:49
@spajce I am not asking for a different solution. I am asking how to solve what I wrote. It is not in the duplicate list. — Abdur Rahim, Dec 14 '12 at 11:51
which one you are suggesting? I am doing the same as the top answer and the rest two is little different. I am not wishing to write direct query. :) — Abdur Rahim, Dec 14 '12 at 11:54
I think you're going to have to filter on your `table_type`, per: [Use SqlConnection.GetSchema to get Tables Only (No Views)](http://stackoverflow.com/questions/13216564/use-sqlconnection-getschema-to-get-tables-only-no-views) — doornail, Dec 14 '12 at 12:09

score 1 · Answer 1 · answered Dec 14 '12 at 11:46

1

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

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

answered Dec 14 '12 at 11:46

Colin Mackay

18,736
7
61
88

thanks, I know about that. but what is wrong with what I wrote? – Abdur Rahim Dec 14 '12 at 11:47

score 1 · Answer 2 · answered Apr 04 '19 at 05:20

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" });

answered Apr 04 '19 at 05:20

KrishnaDhungana

2,604
4
25
37

Here is the relevant documentation: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-schema-collections. Quote: *"**TABLE_TYPE**, String, Type of table. Can be **VIEW** or **BASE TABLE**."* – Heinzi May 04 '19 at 11:04

Getting all Table name in a database with c#

2 Answers2