Início / dba / Perguntas / 335251
Accepted
J. Mini
Asked: 2024-01-28 01:37:52 +0800 CST

Como posso saber quantas tabelas meu DELETE mudou?

  • 772

Suponha que eu tenha muitas tabelas vinculadas por uma cadeia de ON DELETE CASCADEchaves estrangeiras. Até onde sei e experiência, a exclusão de uma tabela não me dá nenhum aviso de que de fato afetei várias tabelas. Por exemplo, se a exclusão de uma linha table1causar a exclusão de 500 linhas, table2tudo o que o SSMS me dirá é

(1 linha afetada)

Existe alguma maneira de saber quantas tabelas/linhas DELETErealmente foram alteradas? Parece que, em teoria, eu poderia excluir uma linha de uma tabela em um banco de dados suficientemente maligno e, sem saber, excluir 99% dos dados do banco de dados.

sql-server

3 respostas

  1. Best Answer

    Se você estiver apenas curioso, dê uma olhada no plano de consulta. Ele mostrará todas as tabelas envolvidas.

    Se você deseja registrá-los ou auditá-los, a história é diferente. Você precisaria usar gatilhos ou algum tipo de auditoria.

    • 3

  2. Você pode obter essas informações do plano de consulta real.

    Se quiser analisar o plano de consulta para descobrir quantas linhas foram realmente afetadas, você pode fazer assim.

    WITH XMLNAMESPACES (
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
  x2.obj.value('@Database', 'sysname'),
  x2.obj.value('@Schema', 'sysname'),
  x2.obj.value('@Table', 'sysname'),
  x1.relop.value('(RunTimeInformation/RunTimeCountersPerThread/@ActualRows)[1]', 'int')
FROM @queryPlan.nodes('//RelOp [@LogicalOp = "Delete"]') x1(relop)
CROSS APPLY x1.relop.nodes('Update/Object') x2(obj);

    banco de dados<> violino

    Se você quiser saber quais tabelas podem ser afetadas pela exclusão de uma determinada tabela, você pode usar a seguinte consulta nos catálogos do sistema.

    WITH fks AS (
    SELECT fk.referenced_object_id
    FROM sys.tables t
    JOIN sys.foreign_keys fk ON fk.parent_object_id = t.object_id
    WHERE t.object_id = OBJECT_ID(N'dbo.SomeTable')
      AND fk.delete_referential_action = 1  -- cascade

    UNION ALL

    SELECT fk.referenced_object_id
    FROM fks
    JOIN sys.foreign_keys fk ON fk.parent_object_id = fks.referenced_object_id
    WHERE fk.delete_referential_action = 1  -- cascade
      AND fk.parent_object_id <> fk.referenced_object_id
)
SELECT
  s.name AS schema_name,
  t.name AS table_name
FROM fks
JOIN sys.tables t ON t.object_id = fks.referenced_object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id;
    • 1

  3. ... Eu poderia excluir uma linha de uma tabela em um banco de dados suficientemente maligno e, sem saber, excluir 99% dos dados do banco de dados.

    Absolutamente correto.

    Uma das maiores ameaças aos nossos bancos de dados é o "Oops!" momento.
    Por exemplo, alguém/alguma coisa disparando a parte errada do SQL que resulta em devastação (se você tiver sorte ; pelo menos isso é fácil de detectar) ou comportamento "estranho" contínuo/progressivo do sistema (o que é muito mais provável). A exclusão acidental do registro "Empresa" no "topo" do sistema financeiro (assim "apagando" efetivamente toda a sua empresa financeiramente) pode ser categorizada como "Oops!" momento (embora, no Reino Unido, provavelmente chamaríamos isso de momento "P45").

    As chaves estrangeiras existem para proteger seus dados.
    Para garantir que, digamos, cada linha de pedido tenha um pedido pai.
    O fato de essas construções SQL também terem sido imbuídas dessas habilidades para alterar ou excluir dados relacionados é, IMHO, um erro .

    As chaves primárias nunca devem mudar.
    Devem ser atribuídos/gerados quando um registo é criado pela primeira vez e devem permanecer inalterados até que esse registo seja final e permanentemente destruído. Se for esse o caso, por que precisaria de " ON UPDATE CASCADE"?

    A destruição de dados deve ser controlada .
    Como você sugeriu, cascatas "aninhadas" podem causar danos horríveis , causando interrupções no sistema, obrigando você a exercer seu plano de recuperação de desastres de banco de dados, etc. É de alto impacto e caro.
    Então "ON CASCADE DELETE" é realmente uma boa ideia?

    Agora, OK, pode haver algumas tabelas "menos importantes" às quais você pode querer aplicar isso, mas isso precisa ser uma decisão consciente, alcançada após discussão e acordo de que esta é realmente a melhor maneira de gerenciar essas tabelas.

    Qualquer pessoa que coloque cláusulas "ON ... CASCADE" em suas cláusulas de chave estrangeira por padrão está apenas carregando seu banco de dados com dinamite.

    • 1

relate perguntas