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.
