Início / dba / Perguntas / 61801
Accepted
magnattic
Asked: 2014-03-27 05:55:55 +0800 CST

Como recuperar a Instância de Captura para uma determinada tabela?

  • 772

Trabalhando com o SQL Server 2012, tenho um layout de tabela hierárquica (TPT). Todas as tabelas "herdam" da mesma tabela base ( dbo.DataObjects). Todas as tabelas têm rastreamento de histórico via CDC ativado.

Estou tentando escrever uma função genérica que retorna as informações do histórico do CDC para um determinado tipo, incluindo as informações relevantes de todas as tabelas base.

Supondo que meu tipo esteja Usercom a tabela dbo.Usersque herda diretamente de dbo.DataObjects, minha consulta deve ser algo como isto:

DECLARE @Begin_LSN binary(10),@End_LSN binary(10) 
SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn('dbo_Users') 
SELECT @End_LSN = sys.fn_cdc_get_max_lsn() 
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_DataObjects(@Begin_LSN, @End_LSN,'ALL') AS a 
JOIN cdc.fn_cdc_get_all_changes_dbo_Users(@Begin_LSN, @End_LSN,'ALL') AS b 
ON a.__$start_lsn = b.__$start_lsn
WHERE ID = 10;

Isso me dará a trilha do histórico completo para o usuário com ID 10, incluindo as colunas da tabela base.

Agora quero construir esse tipo de consulta dinamicamente.

Um dos meus problemas é que as funções do cdc para recuperar as informações do histórico incluem o nome da instância de captura:

cdc.fn_cdc_get_all_changes_<capture_instance>

Também preciso do nome da instância de captura na segunda linha para obter o min lsn:

sys.fn_cdc_get_min_lsn('<capture_instance>')

O nome da instância de captura é criado automaticamente para mim quando habilito o CDC para uma tabela ou é passado manualmente. Se eu não quiser acompanhar o nome da instância de captura, existe uma maneira de recuperar o nome da instância de captura para uma determinada tabela ?

Estou ciente de que pode haver mais de uma instância de captura por tabela (no máximo 2, eu acho), mas provavelmente sempre usarei apenas uma por tabela.

PS: Também estou ciente de que o nome da instância de captura geralmente é construído no caminho <schema>_<table>, mas confiar nessa suposição parece um pouco arriscado. A convenção de nomenclatura pode mudar para versões futuras ou alguém pode fornecer um nome de instância de captura manual/alterar o nome.

sql-server change-data-capture

2 respostas

  1. Best Answer

    Entrada (espero que parâmetros de procedimentos armazenados):

    DECLARE @table1 NVARCHAR(513) = N'dbo.Users',
        @table2 NVARCHAR(513) = N'dbo.DataObjects',
        @ID     INT = 10;

    Código:

    DECLARE @InstanceName1 NVARCHAR(513),
        @InstanceName2 NVARCHAR(513),
        @Begin_LSN     BINARY(10),
        @End_LSN       BINARY(10);

SELECT @InstanceName1 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table1,1)
    AND s.name = PARSENAME(@table1,2);

SELECT @InstanceName2 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table2,1)
    AND s.name = PARSENAME(@table2,2);

SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn(@InstanceName1), 
       @End_LSN   = sys.fn_cdc_get_max_lsn();

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT * 
  FROM cdc.fn_cdc_get_all_changes_' + @InstanceName2
  + '(@b, @l, ''ALL'') AS a 
  INNER JOIN cdc.fn_cdc_get_all_changes_' + @InstanceName1
  + '(@b, @l, ''ALL'') AS b 
  ON a.__$start_lsn = b.__$start_lsn
  WHERE a.ID = @ID;';
  ------^ guessing here

EXEC sp_executesql @sql, 
  N'@b BINARY(10), @l BINARY(10), @ID INT',
  @Begin_LSN, @End_LSN, @ID;
    • 6

  2. A função object_id() é sua amiga:

    declare @capture_instance sysname
select @capture_instance = capture_instance 
from cdc.change_tables where source_object_id = object_id('dbo.myTable)
    • 2

relate perguntas