Identify table types in SQL Server 2016

This query works on SQL Server 2016 or newer.

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)


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


  • 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


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

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.