Capturando data e hora de alteração no CDC do SQL Server

Lembre-se de que o CDC usa um agente de leitura de log para preencher a tabela de alterações. Por que isso é importante? Por esse mecanismo, as linhas aparecem nas tabelas de alteração de forma assíncrona com as alterações feitas nas tabelas base.

Na verdade, existem 3 pontos de tempo diferentes que podem ser registrados, em ordem cronológica inversa:

1. A hora em que a alteração foi entregue na mesa de alteração (que é o que você está registrando).
2. A hora em que a transação que continha a alteração foi confirmada (usando cdc.lsn_time_mapping).
3. A hora com a qual você preenche manualmente uma coluna na tabela base (usando uma restrição padrão, um acionador, etc.).

Portanto, a primeira coisa é ter claro o que você deseja gravar. Normalmente, nos importaríamos com o número 2 ou com o número 3.

Se o mecanismo de mapeamento LSN (nº 2) não estiver funcionando bem o suficiente para você, a única alternativa compatível é adicionar uma coluna à tabela base e preenchê-la você mesmo (nº 3).

No que diz respeito à alteração das tabelas internas, por uma questão de política, acho melhor evitar hackear com as internas quando houver alternativas suportadas. A última coisa que você deseja é um importante sistema de produção caindo, precisando ligar para o suporte ao produto e ter o serviço negado por causa de algo assim. Não importa os problemas de potencialmente quebrar coisas (atualizações) ou ser quebrado porque foi inesperado (desligue o CDC e ligue-o novamente, conforme mencionado na outra resposta).

Um exemplo prático:

USE Database;
GO

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('schema_tablename')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time'
    ,[Field1]
    ,[Field2]
    ,[Field3]
FROM [cdc].[fn_cdc_get_all_changes_schema_tablename]
  (@from_lsn, @to_lsn, N'all');

A única ressalva que eu daria é que essas tabelas são descartadas automaticamente quando o CDC é desabilitado. A coluna não é recriada automaticamente quando você a reativa

Inspirado na resposta de Gareth, aqui está uma versão mais curta. Isso não usa, o DECLAREque é ótimo se você precisar criar um VIEW( DECLAREnão é permitido entrar VIEW)

SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time'
    , *
FROM [cdc].[fn_cdc_get_all_changes_dbo_yourtablename
    (sys.fn_cdc_get_min_lsn('dbo_yourtablename')
    , sys.fn_cdc_get_max_lsn(), N'all')

Com base nas respostas anteriores, criei este script que examinará todas as tabelas CDC e exibirá o nome da tabela e a última atualização:

DECLARE @tableName varchar(max)
DECLARE @from_lsn binary(10)
DECLARE @to_lsn binary(10) = sys.fn_cdc_get_max_lsn()
DECLARE @results TABLE
(
    [Table] varchar(max),
    [LastUpdated] datetime
)

-- Get list of change tables
DECLARE cdc_tables_cursor CURSOR
FOR SELECT
        [capture_instance]
    FROM cdc.change_tables;

OPEN cdc_tables_cursor
FETCH NEXT FROM cdc_tables_cursor INTO @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Get Table Name / Last Updated
    SET @from_lsn = sys.fn_cdc_get_min_lsn(@tableName)
    DECLARE @sql nvarchar(max) = '
    SELECT
        top 1
        REPLACE(@tableName,''dbo_'','''') as [Table],
        sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS ''Time''
    FROM [cdc].[fn_cdc_get_all_changes_'+@tableName+'](@from_lsn, @to_lsn, N''all update old'')
    order by 2 desc'

    -- Insert results of dynamic sql
    INSERT @results
    EXECUTE sp_executesql   
        @sql,  
        N'@tableName varchar(max), @from_lsn binary(10), @to_lsn binary(10)',  
        @tableName, @from_lsn, @to_lsn;

    FETCH NEXT FROM cdc_tables_cursor INTO @tableName
END

CLOSE cdc_tables_cursor;

DEALLOCATE cdc_tables_cursor;

-- List Tables/Last Updated
SELECT 
    * 
FROM @results
order by 1