View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0006811SymmetricDS ProImprovementpublic2025-04-09 16:492025-04-09 16:49
Reporterpbelov Assigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.16.0 
Summary0006811: Exclude history tables generated by MS SQL Server for change capture and audit
DescriptionHistory tables are generated by MS SQL Server for change capture and audit.
They are not "real" tables and should be excluded from:
* New table trigger wizard.
* Add tables to replication on Canvas.
* Explorer tables tree view.

It will also be helpful to have a parameter to turn off this filter, in a rare case customer actually needs access to all history tables.

See also:
https://dataedo.com/kb/query/sql-server/list-temporal-tables-in-database
https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16
Steps To ReproduceCREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

-- The dbo.EmployeeHistory table should not be shown to SymmetricDS customers at all (it is not a source of changes)


select schema_name(t.schema_id) as temporal_table_schema,
     t.name as temporal_table_name,
    schema_name(h.schema_id) as history_table_schema,
     h.name as history_table_name,
    case when t.history_retention_period = -1
        then 'INFINITE'
        else cast(t.history_retention_period as varchar) + ' ' +
            t.history_retention_period_unit_desc + 'S'
    end as retention_period
from sys.tables t
    left outer join sys.tables h
        on t.history_table_id = h.object_id
where t.temporal_type = 2
order by temporal_table_schema, temporal_table_name
TagsNo tags attached.

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2025-04-09 16:49 pbelov New Issue