SQL Server 2016 brought new features and table types - temporal tables and external tables.

Query below lists tables in current database and identifies it's type. Types include:

• Plain old Regular table
• System versioned table (temporal table) (introduced in SQL Server 2016)
• History table (introduced in SQL Server 2016)
• PolyBase External table (introduced in SQL Server 2016)
• File table (introduced in SQL Server 2012)

Query

select schema_name(schema_id) as schema_name,
       name as table_name,
        case when is_external = 1 then 'External table'
            when temporal_type = 2 then 'System versioned table'
            when temporal_type = 1 then 'History table'
            when is_filetable = 1 then 'File table'
            else 'Regular table'
        end as table_type
        from sys.tables
order by schema_name, table_name

Columns

• schema_name - schem name of the table
• table_name - table name
• table_type - type of the table identified by the query. Values include:
  • Regular table
  • System versioned table
  • History table
  • External table
  • File table

Rows

• One row represents one table in a database
• Scope of rows: all tables in a database
• Ordered by schema and table name

Sample results