none
Verificando alteração em uma determinada tabela em um banco SQL Server 2008. RRS feed

  • Pergunta

  • Bom dia estou com um problema , onde trabalho tem um sistema que usa uma base SqlServer 2008 r2 express.

    porem as tabelas e os campos das instancia é difícil de se compreender os nomes dos bancos e das tabelas, gostaria de saber.

    sé tem como encontrar qual banco e suas respectivas tabelas sofreu alguma inserção de valores.     

     Agradeço desde já. 
    quarta-feira, 13 de setembro de 2017 13:56

Respostas

  • Robert,

    Duas soluções que você poderia tentar utilizar:

    - Change Tracking; e

    - Change Data Capture.

    Ambas introduzidas a partir do SQL Server 2008 que permitem justamente fazer este tipo de análise e monitoramento que esta sendo alterado em nossas tabelas.

    Veja os exemplos abaixo:

    --create test DB
    USE master;
    GO
    
    CREATE DATABASE DB_test;
    GO
    
    ALTER DATABASE DB_test SET
    CHANGE_TRACKING = ON
     (AUTO_CLEANUP = ON,          -- automatic tracking table clean up process
      CHANGE_RETENTION = 1 HOURS  -- specify the time frame for which tracked information will be maintained -- 
    );
    GO
    
    --create test table
    USE DB_test;
    GO
    
    CREATE TABLE dbo.tb
    (id int
     CONSTRAINT PK_tb_id PRIMARY KEY,
     col1 int,
     col2 varchar(10),
     col3 nvarchar(max),
     col4 varbinary(max));
    GO
    
    ALTER TABLE dbo.tb
    ENABLE CHANGE_TRACKING
     WITH(TRACK_COLUMNS_UPDATED = ON  -- With this option, you can include columns also whose values were changed
     );
    GO
    
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion;
    GO
    
    -- testing
    
    -- a.insert data 
    INSERT dbo.tb(id, col1, col2, col3, col4)
    VALUES(1,1, 'AA', 'AAA', 0x1),
    	  (2,2, 'BB', 'BBB', 0x2),
          (3,3, 'CC', 'CCC', 0x2);
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;
     
    -- b. update data 
    BEGIN TRAN;
    UPDATE dbo.tb SET
    col1 = 11
    WHERE id = 1;
     
    UPDATE dbo.tb SET
    col1 = 111
    WHERE id = 1;
    COMMIT TRAN;
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;

    -- Habilitando o CDC para o Banco de Dados --
    Use SQLMagazine
    Go
    
    Exec sys.sp_cdc_enable_db
    Go
    
    -- Desabilitando o CDC para o Banco de Dados --
    Use SQLMagazine
    Go
    
    Exec sys.sp_cdc_disable_db
    Go
    
    -- Criando a Tabela de Exemplo --
    Create Table Produtos
     (Codigo Int Identity(1,1),
       Descricao VarChar(20))
    Go
    
    -- Adicionando a Chave Primaria --
    Alter Table Produtos
        Add Constraint [PK_Codigo_Produtos] Primary Key (Codigo)
    Go
    
    -- Inserindo a Massa de Registros para Teste --   
    Declare @ContadorRegistros Int
    Set @ContadorRegistros=1
    
    While @ContadorRegistros <=1000
     Begin
     
      If @ContadorRegistros =1
       Insert Into Produtos Values ('Produto Nº: 1')
      Else
       Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
       
       Set @ContadorRegistros += 1;
     End
     
    -- Visualizando os Dados --   
    Select * from Produtos
          
    -- Habilitando o Change Data Capture para trabalhar sobre a table Produtos --
    EXECUTE sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name = N'Produtos',
        @role_name = N'cdc_Admin';
    GO
    
    -- Retornando todas as linhas capturadas pelo CDC --
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(10)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Produtos
      (@from_lsn, @to_lsn, N'all')
    Go
    
    -- Inserindo novos dados --
    Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
    Go
    
    -- Atualizando dados já existentes --
    Update Produtos
    Set Descricao= Descricao+' - Upd'
    Where Codigo Between 11 And 21
    Go
    
    -- Retornando todas as linhas capturadas pelo CDC com Net Changes--
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(20)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Produtos
                                  (@from_lsn, @to_lsn, N'all')
    GO
    
    -- Retornando as colunas utilizadas pelo CDC para Captura --
    Execute sys.sp_cdc_get_captured_columns 
                             @capture_instance = N'dbo_Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de uma tabela específica --
    Execute sys.sp_cdc_help_change_data_capture 
                              @source_schema = N'dbo', 
                              @source_name = N'Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de todas as tabelas --
    EXECUTE sys.sp_cdc_help_change_data_capture;


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 14 de setembro de 2017 19:07

Todas as Respostas

  • Sua versão é Express, isso impossibilita de habilitar Auditoria, caso precise de algo pontual no futuro utilize a documentação
    https://msdn.microsoft.com/en-us/library/dd392015.aspx.

    Porém caso seja o desenvolvedor do Banco de dados pode utilizar os TRIGGERS, um pouco mais de trabalho porém funciona para ter um melhor controle. A performance degrada um pouco no uso desta opção, porém é funcional.

    Eu li um artigo bom que fala sobre o assunto :

    https://www.dirceuresende.com/blog/sql-server-como-criar-um-historico-de-alteracoes-de-dados-para-suas-tabelas-logs-auditoria/

    Agora caso não possa alterar nada no seu Banco devido a permissões ou regras de negócio tem que partir para consultas que vão lhe informar e ir verificando.

    Já ouviu falar das DMs ? Estude sobre as mesmas, aqui vai uma dica.

    SELECT * FROM sys.dm_db_index_physical_stats
        (DB_ID(N'AdventureWorks2014'), OBJECT_ID(N'Person.Person'), NULL, NULL , 'DETAILED');

    E por último verifica esse código abaixo para ter algo pontual e pode ir jogando em uma tabela temporária e analisando.

    SELECT
        t.NAME AS Entidade,
        p.rows AS Registros,
        SUM(a.total_pages) * 8 AS EspacoTotalKB,
        SUM(a.used_pages) * 8 AS EspacoUsadoKB,
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB
    FROM
        sys.tables t
    INNER JOIN
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE
        t.NAME NOT LIKE 'dt%'
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255
    GROUP BY
        t.Name, s.Name, p.Rows
    ORDER BY
        Registros DESC

    ;)

    Aprendendo e progredindo...

    Alessandro Garcia

    quarta-feira, 13 de setembro de 2017 15:27
  • Sua versão é Express, isso impossibilita de habilitar Auditoria, caso precise de algo pontual no futuro utilize a documentação
    https://msdn.microsoft.com/en-us/library/dd392015.aspx.

    Porém caso seja o desenvolvedor do Banco de dados pode utilizar os TRIGGERS, um pouco mais de trabalho porém funciona para ter um melhor controle. A performance degrada um pouco no uso desta opção, porém é funcional.

    Eu li um artigo bom que fala sobre o assunto :

    https://www.dirceuresende.com/blog/sql-server-como-criar-um-historico-de-alteracoes-de-dados-para-suas-tabelas-logs-auditoria/

    Agora caso não possa alterar nada no seu Banco devido a permissões ou regras de negócio tem que partir para consultas que vão lhe informar e ir verificando.

    Já ouviu falar das DMs ? Estude sobre as mesmas, aqui vai uma dica.

    SELECT * FROM sys.dm_db_index_physical_stats
        (DB_ID(N'AdventureWorks2014'), OBJECT_ID(N'Person.Person'), NULL, NULL , 'DETAILED');

    E por último verifica esse código abaixo para ter algo pontual e pode ir jogando em uma tabela temporária e analisando.

    SELECT
        t.NAME AS Entidade,
        p.rows AS Registros,
        SUM(a.total_pages) * 8 AS EspacoTotalKB,
        SUM(a.used_pages) * 8 AS EspacoUsadoKB,
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB
    FROM
        sys.tables t
    INNER JOIN
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE
        t.NAME NOT LIKE 'dt%'
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255
    GROUP BY
        t.Name, s.Name, p.Rows
    ORDER BY
        Registros DESC

    ;)

    Aprendendo e progredindo...

    Alessandro Garcia

    muito obrigado, vou dar uma olhada. 
    quarta-feira, 13 de setembro de 2017 18:16
  • Robert,

    Duas soluções que você poderia tentar utilizar:

    - Change Tracking; e

    - Change Data Capture.

    Ambas introduzidas a partir do SQL Server 2008 que permitem justamente fazer este tipo de análise e monitoramento que esta sendo alterado em nossas tabelas.

    Veja os exemplos abaixo:

    --create test DB
    USE master;
    GO
    
    CREATE DATABASE DB_test;
    GO
    
    ALTER DATABASE DB_test SET
    CHANGE_TRACKING = ON
     (AUTO_CLEANUP = ON,          -- automatic tracking table clean up process
      CHANGE_RETENTION = 1 HOURS  -- specify the time frame for which tracked information will be maintained -- 
    );
    GO
    
    --create test table
    USE DB_test;
    GO
    
    CREATE TABLE dbo.tb
    (id int
     CONSTRAINT PK_tb_id PRIMARY KEY,
     col1 int,
     col2 varchar(10),
     col3 nvarchar(max),
     col4 varbinary(max));
    GO
    
    ALTER TABLE dbo.tb
    ENABLE CHANGE_TRACKING
     WITH(TRACK_COLUMNS_UPDATED = ON  -- With this option, you can include columns also whose values were changed
     );
    GO
    
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion;
    GO
    
    -- testing
    
    -- a.insert data 
    INSERT dbo.tb(id, col1, col2, col3, col4)
    VALUES(1,1, 'AA', 'AAA', 0x1),
    	  (2,2, 'BB', 'BBB', 0x2),
          (3,3, 'CC', 'CCC', 0x2);
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;
     
    -- b. update data 
    BEGIN TRAN;
    UPDATE dbo.tb SET
    col1 = 11
    WHERE id = 1;
     
    UPDATE dbo.tb SET
    col1 = 111
    WHERE id = 1;
    COMMIT TRAN;
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;

    -- Habilitando o CDC para o Banco de Dados --
    Use SQLMagazine
    Go
    
    Exec sys.sp_cdc_enable_db
    Go
    
    -- Desabilitando o CDC para o Banco de Dados --
    Use SQLMagazine
    Go
    
    Exec sys.sp_cdc_disable_db
    Go
    
    -- Criando a Tabela de Exemplo --
    Create Table Produtos
     (Codigo Int Identity(1,1),
       Descricao VarChar(20))
    Go
    
    -- Adicionando a Chave Primaria --
    Alter Table Produtos
        Add Constraint [PK_Codigo_Produtos] Primary Key (Codigo)
    Go
    
    -- Inserindo a Massa de Registros para Teste --   
    Declare @ContadorRegistros Int
    Set @ContadorRegistros=1
    
    While @ContadorRegistros <=1000
     Begin
     
      If @ContadorRegistros =1
       Insert Into Produtos Values ('Produto Nº: 1')
      Else
       Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
       
       Set @ContadorRegistros += 1;
     End
     
    -- Visualizando os Dados --   
    Select * from Produtos
          
    -- Habilitando o Change Data Capture para trabalhar sobre a table Produtos --
    EXECUTE sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name = N'Produtos',
        @role_name = N'cdc_Admin';
    GO
    
    -- Retornando todas as linhas capturadas pelo CDC --
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(10)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Produtos
      (@from_lsn, @to_lsn, N'all')
    Go
    
    -- Inserindo novos dados --
    Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
    Go
    
    -- Atualizando dados já existentes --
    Update Produtos
    Set Descricao= Descricao+' - Upd'
    Where Codigo Between 11 And 21
    Go
    
    -- Retornando todas as linhas capturadas pelo CDC com Net Changes--
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(20)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Produtos
                                  (@from_lsn, @to_lsn, N'all')
    GO
    
    -- Retornando as colunas utilizadas pelo CDC para Captura --
    Execute sys.sp_cdc_get_captured_columns 
                             @capture_instance = N'dbo_Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de uma tabela específica --
    Execute sys.sp_cdc_help_change_data_capture 
                              @source_schema = N'dbo', 
                              @source_name = N'Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de todas as tabelas --
    EXECUTE sys.sp_cdc_help_change_data_capture;


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 14 de setembro de 2017 19:07