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:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
    IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
        DELETE FROM ?
    ELSE
        TRUNCATE TABLE ?'
GO

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'SELECT * FROM ?'
GO

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.