The SSMS Database Properties screen has a page "Change Tracking". How can I get those properties via regular SQL? The normal way to return database properties is to use sys.databases or DATABASEPROPERTYEX, but I could not find those properties via either approach.
The SQL instance is 2019 - Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020
View sys.change_tracking_databases returns the db names of all dbs that have change tracking enabled.
Thus, the db name will not be in the view if change tracking is off.
SELECT CASE WHEN ctd.database_id IS NULL THEN 'OFF' ELSE 'ON' END AS change_tracking, *
FROM sys.databases d
LEFT OUTER JOIN sys.change_tracking_databases ctd ON ctd.database_id = d.database_id
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 26, 2021 at 9:09 pm
ScottPletcher,
Thank you. This is exactly what I need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply