search

martinjw / dbschemareader

Read database metadata (from SqlServer/Oracle/MySql/SQLite/PostgreSql/DB2 etc) into one simple model
Microsoft Public License
293 stars 128 forks source link

Determine whether DatabaseTable is a view #113

Closed thomasjoscht closed 4 years ago

thomasjoscht commented 4 years ago

Hey, I need to identify the type of DatabaseTable, whether it is a view or not. Simple check for table is DatabaseView does not work.

I'm mostly using DatabaseReader.TablesQuickView or DatabaseReader.TableList for huge databases because of better performance. Both will give me a list of all tables inclusive views for some DbProvider like PostgreSQL. Without calling DatabaseReader.AllViews I don't know which of these entries are real tables or views.

Will it be possible to add a new property to DatabaseTable which allows to identify the type? In ANSI SQL-92 the field TABLE_TYPE of INFORMATION_SCHEMA.TABLES should contain "BASE TABLE" or "VIEW". Do you have any experience with this field?

My suggestions are:

  1. Introducing a new property "String TableType" or "String DbTableType" which contains the information of field TABLE_TYPE in information schema. This will be the simplest implementation for the framework.
  2. Introducing a new property "Boolean IsView" which should be true in case it is a DatabaseView or not. This could be perhaps determined via SQL: INFORMATION_SCHEMA.TABLES.TABLE_TYPE = "VIEW" or via SQL join with INFORMATION_SCHEMA.VIEWS. I think the second solution via join will be more reliable because TABLE_TYPE could contain unpredicted values.

What do you think? Regards Thomas

martinjw commented 4 years ago

SqlServer uses almost exactly the same sql, but includes a where clause- TABLE_TYPE='BASE TABLE'

The PostgreSql provider should do the same - so whichever database, TablesQuickView or TableList should return just tables, not views.