T-SQL

Mazání dat pomocí sp_MSForEachTable

Pomocí nedokumentované procedury sp_MSForEachTable můžeme spouštět dotazy a operace nad všemi tabulkami v databázi. Že je procedura nedokumentovaná znamená, že není zmíněna v SQL Server dokumentaci (BOL) a je oficiálně určena pouze k internímu použití. Na druhou stranu je procedura používání již spoustu let i běžnými uživateli SQL Serveru a je možné ji efektivně použít tam, kde bychom jinak museli psát kód s využitím WHILE cyklu nebo kurzorů. Její použití si ukážeme na jednoduchém scénáři: smazání dat ze všech tabulek v databázi.Nejprve se podívejme na samotný skript:

Skript si umí poradit s mazáním i v případě, že databáze obsahuje cizí klíče, které za normálních okolností brání smazání dat z tabulky, pokud jsou použita jako cizí klíč v jiné tabulce. V prvním kroku se proto volá sp_MSForEachTable s ALTER TABLE NO CHECK CONSTRAINT ALL, který zakáže všechna omezení na tabulkou, pro kterou je volán. Jméno tabulky do skriptu doplňuje automaticky právě sp_MSForEachTable, a to do místa, kde je vložen ?. Po smazání se obdobným způsobem omezení opět povolí.

Samotné mazání lze provést dvěma způsoby: DELETE nebo TRUNCATE. Pro TRUNCATE platí omezení, že tento příkaz nelze použít pro tabulky, které mají cizí klíče, a to ani v případě, že jsme cizí klíče zakázali, protože jejich definice stále zůstává uložena v metadatech. TRUNCATE je samozřejmě výrazně rychlejší a byla by škoda si vystačit jen s DELETE. Proto musíme zjistit, jestli právě mazaná tabulka cizí klíče obsahuje či nikoliv a podle toho rozhodnout, zda se použije DELETE nebo TRUNCATE. Pomocí OBJECTPROPERTY() zjistíme nastavení vlastnosti  TableHasForeignRef, a pokud je 1, zavoláme DELETE, pokud 0, pak TRUNCATE.

V posledním kroku použijeme sp_MSForEachTable ještě jednou pro kontrolu, že v žádné tabulce nezůstaly nějaké řádky.

Leave a Reply

Your email address will not be published. Required fields are marked *