Identify table types in SQL Server 2017

This query works on SQL Server 2017 or newer.

SQL Server 2016 and 2017 brought new features and table types - temporal tables (2016), external tables (2016) and graph tables (2017).

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

  • Plan old Regular table
  • Graph node table (introduced in SQL Server 2017)
  • Graph edge table (introduced in SQL Server 2017)
  • 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 is_node = 1 then 'Graph node table'
            when is_edge = 1 then 'Graph edge 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
    • Graph node table
    • Graph edge 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.