Usuń wszystkie tabele tymczasowe

Ile razy zdarzyło Ci się, że dostajesz od kogoś kod wykorzystujący tabele tymczasowe, uruchamiasz go raz – działa, potem drugi i dostajesz komunikat:

Msg 2714, Level 16, State 6, Line 19
There is already an object named '#nazwa_tabeli' in the database.

Albo wyciągasz kod wykorzystujący tabele tymczasowe z procedury, aby w nim podłubać, gdzie nie ma usuwania tabel tymczasowych (bo nie musi być) drugie uruchomienie i zonk.
Brak usuwania tabel tymczasowych i zaczyna się zbieranie wszystkich nazw, żeby dopisać wymagany fragment do usuwania.
Problem zaczyna się, gdy kod jest obszerny, a tabel tymczasowych paręnaście lub więcej.
Dlatego napisałem generator do usuwania wszystkich tymczasowych tabel w danej sesji, można go dodać, jako Snippet w SSMS lub użyć w Auto Replacements w takim dodatku jak np.: SSMSBoost.

Generator dodatkowo print-uje kod usuwający wykryte tabele, jeżeli byśmy chcieli użyć go później w naszym kodzie.

DECLARE @dtt NVARCHAR(MAX) = 
(SELECT 
    (SELECT 'IF OBJECT_ID(''tempdb..'  
		  + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1) 
		  + ''') IS NOT NULL DROP TABLE ' 
		  + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1)  
		  + ';' + CHAR(10)
       FROM tempdb.sys.tables AS t
      WHERE t.name LIKE '%[_][_][_]%'
        AND t.[object_id] = OBJECT_ID('tempdb..' 
		  + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1))
        FOR XML PATH('')));
PRINT @dtt;
EXEC sp_executesql @dtt;
GO

Zainspirowane wątkiem na stack overflow: Temp tables on the current connection

2 thoughts on “Usuń wszystkie tabele tymczasowe

  1. Lukasz pisze:

    Ciekawe, pozwoliłem sobie przepisać to na wersję 2017:

    DECLARE @dtt NVARCHAR(MAX) =
    (SELECT CONCAT(‘DROP TABLE IF EXISTS ‘, STRING_AGG(name, ‘,’))
    FROM tempdb.sys.tables AS t
    WHERE t.name LIKE ‘%[_][_][_]%’
    AND t.[object_id] = OBJECT_ID(‘tempdb..’
    + SUBSTRING(t.name, 1, CHARINDEX(‘___’, t.name) – 1)));

    PRINT @dtt;
    EXEC sp_executesql @dtt;

    Demo:
    http://dbfiddle.uk/?rdbms=sqlserver_next&fiddle=ab6160b4b07ae6095da285930f068883

    W ramach rozwoju tego skryptu wartoby dodać sprawdzanie czy tabla została stworzona przez danego użytkownika/@@SPID. W obecnej wersji nadaje się na środowisko DEV. Głupioby było ususwać globalnie tabele tymczasowe innych użytkowników 🙂

    1. Michał Gołoś pisze:

      Łukasz, dzięki za napisanie wersji na 2017. Tak jak rozmawialiśmy skrypt nie kasuje tabel innych użytkowników, widoczność tabel tymczasowych jest w obrębie sesji.

Pozostaw odpowiedź Lukasz Anuluj pisanie odpowiedzi

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *