How can I identify only base tables when iterating through all tables in SQL smo?

Question

This is my code:

Server server = new Server(new ServerConnection(con));
server.ConnectionContext.Connect();

foreach (Table table in server.Databases[sqlDatabase].Tables)
{
    if (table <is a base table>)

Basically this pulls back views as well I think. The SQL I use to identify the wanted tables is:

SELECT TABLE_NAME FROM MyDatabase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

However there doesn't seem to be a TABLE_TYPE property on the results which are Microsoft.SqlServer.Management.Smo.TableCollection. According to SQL, the TABLE_TYPE is either BASE TABLE or VIEW so only two possibilities.

How can I identify only tables (and not views)?

Answer 1

Looping over the TablesCollection will not enumerate the Views, so your code should works as it is without worrying about Views.

Indeed you have a Views collection as server.Databases[sqlDatabase].Views

And, in this collection as well as in the Tables collection, you could differentiate your own views/tables from the system views/tables using the property IsSystemObject

var myTables = server.Databases[sqlDatabase].Views
                    .Cast<Table> => !v.IsSystemObject));

var myViews = server.Databases[sqlDatabase].Views
                    .Cast<View>().Where(v => !v.IsSystemObject));

Answer 2

It's okay what you did. You can find views this way -> WHERE TABLE_TYPE = 'VIEW', so your select do what you want. I recommend you to open management studio or any other program that allows you to query data and run this:

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