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 t.name AS CDCTables

from sys.tables t

where t.is_tracked_by_cdc = 1

 

— File Tables — SQL 2012 +

select distinct t.name AS FileTables

from sys.tables t

where t.is_filetable = 1

 

— Temporal Tables — SQL 2016 +

select distinct t.name AS TemporalTables

from sys.tables t

where t.temporal_type > 0

 

— Stretch Enabled Tables — SQL 2016 +

select distinct t.name AS StretchTables

from sys.tables t

where t.is_remote_data_archive_enabled > 0

 

— External Tables — SQL 2016 +

select distinct t.name AS ExternalTables

from sys.tables t

where t.is_external > 0

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server

https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/enable-stretch-database-for-a-table

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql

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

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: