Find Tables That Have Special Features Enabled

Find out if any of tables in your database have special features enabled using the queries below.  These features need to be understood and carefully managed.

— CDC Enabled Tables

select distinct AS CDCTables

from sys.tables t

where t.is_tracked_by_cdc = 1


— File Tables — SQL 2012 +

select distinct AS FileTables

from sys.tables t

where t.is_filetable = 1


— Temporal Tables — SQL 2016 +

select distinct AS TemporalTables

from sys.tables t

where t.temporal_type > 0


— Stretch Enabled Tables — SQL 2016 +

select distinct AS StretchTables

from sys.tables t

where t.is_remote_data_archive_enabled > 0


— External Tables — SQL 2016 +

select distinct AS ExternalTables

from sys.tables t

where t.is_external > 0

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at We will be happy to help! Leave a comment and feel free to track back to us. Visit us at!

Both comments and trackbacks are currently closed.
%d bloggers like this: