Eliminar tablas temporales locales en un procedimiento de SQL almacenado

En esta última parte de una serie de tres partes, veremos cómo crear un script, envolverlo en un procedimiento almacenado y usarlo para eliminar todos los horarios locales y un horario local específico.

En secciones anteriores de esta serie, analizamos las tablas temporales e identificamos sus diferencias con las variables tabulares, y luego pasamos a aprender los conceptos básicos de las tablas de SQL Server.

Índice

    Eliminar tablas temporales locales

    Para eliminar todas las tablas temporales SQL locales, primero debemos encontrar su ubicación. Podemos hacer esto usando el siguiente escenario:

     SELECT [t].[name], [t].[object_id]
     FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
     WHERE [t].[name] LIKE '#[^#]%'
     AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
     AND [t].[type] = 'U';

    La salida es la siguiente:

    Aquí obtenemos una lista de nombres locales de tablas temporales y sus identificadores.

    Dado que SSMS 18 no tiene un depurador incorporado, apliquemos dbForge Studio para SQL Server para depurar el código.

    Escribe el siguiente código:

     CREATE TABLE #MyLocalTempTable1 ([ID] INT);
     CREATE TABLE #MyLocalTempTable2 ([ID] INT);
    
     DECLARE @tbl_name NVARCHAR(255);
     DECLARE @object_id INT;
     DECLARE @tsql NVARCHAR(2000);
    
     DECLARE sql_cursor CURSOR LOCAL FOR
     SELECT [t].[name], [t].[object_id]
     FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
     WHERE [t].[name] LIKE '#[^#]%'
     AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
     AND [t].[type] = 'U';
     OPEN sql_cursor;
     FETCH NEXT FROM sql_cursor   
     INTO @tbl_name,
          @object_id;
     WHILE (@@FETCH_STATUS = 0)
     BEGIN
       SET @tsql=N'DROP TABLE '+QUOTENAME(@tbl_name);
    
       EXEC [sys].[sp_executesql] @tsql;
    
       FETCH NEXT FROM sql_cursor   
       INTO @tbl_name,
            @object_id;
     END
    
     CLOSE sql_cursor;
     DEALLOCATE sql_cursor;

    Creamos 2 tablas temporales locales y 3 variables para su posterior procesamiento y eliminación final de todas las tablas de sesión temporales locales.

    Ahora vamos a depurar el fragmento de código. Escoger Depuración -> Iniciar desde el menú principal:

    Después de eso, podemos verificar secuencialmente el código en la esquina inferior izquierda de la pantalla (en Relojes tab) y mostrar los valores de los parámetros requeridos (@tbl_name, @object_id, @tsql):

    De esta manera, podemos controlar la solución en cada pieza de código.

    Ahora cambiemos nuestro código. Agregaremos una opción para determinar qué tabla SQL local queremos eliminar:

     CREATE TABLE #MyLocalTempTable1 ([ID] INT);
     CREATE TABLE #MyLocalTempTable2 ([ID] INT);
    
     DECLARE @table_name NVARCHAR(255);
    
     SET @table_name="#MyLocalTempTable2";
    
     DECLARE @tbl_name NVARCHAR(255);
     DECLARE @object_id INT;
     DECLARE @tsql NVARCHAR(2000);
    
     DECLARE sql_cursor CURSOR LOCAL FOR
     SELECT [t].[name], [t].[object_id]
     FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
     WHERE [t].[name] LIKE '#[^#]%'
     AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
     AND [t].[type] = 'U'
     AND (([t].[object_id] = object_id(N'tempdb..' + @table_name)) OR(@table_name IS NULL));
     OPEN sql_cursor;
     FETCH NEXT FROM sql_cursor   
     INTO @tbl_name,
          @object_id;
     WHILE (@@FETCH_STATUS = 0)
     BEGIN
       SET @tsql=N'DROP TABLE '+QUOTENAME(@tbl_name);
    
       EXEC [sys].[sp_executesql] @tsql;
    
       FETCH NEXT FROM sql_cursor   
       INTO @tbl_name,
            @object_id;
     END
    
     CLOSE sql_cursor;
     DEALLOCATE sql_cursor;
     

    Nos detenemos inmediatamente después de establecer el valor de la variable @tsql. Después de eso ejecutamos la depuración:

    Esto eliminará solo la tabla temporal local definida en el código.

    Ahora ajustemos nuestra solución para DropSessionLocalTempTables en un formato de procedimiento guardado de la siguiente manera:

     CREATE PROCEDURE [dbo].[DropSessionLocalTempTables]
         @table_name NVARCHAR(255) = NULL
     AS
     BEGIN
         SET NOCOUNT ON;
         DECLARE @tbl_name NVARCHAR(255);
         DECLARE @object_id INT;
         DECLARE @tsql NVARCHAR(2000);
         DECLARE sql_cursor CURSOR LOCAL FOR
         SELECT [t].[name], [t].[object_id]
         FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
         WHERE [t].[name] LIKE '#[^#]%'
         AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
         AND [t].[type] = 'U'
         AND (([t].[object_id] = object_id(N'tempdb..' + @table_name)) OR(@table_name IS NULL));
         OPEN sql_cursor;
         FETCH NEXT FROM sql_cursor   
         INTO @tbl_name,
              @object_id;
         WHILE (@@FETCH_STATUS = 0)
         BEGIN
           SET @tsql=N'DROP TABLE '+QUOTENAME(@tbl_name);
           EXEC [sys].[sp_executesql] @tsql;
           FETCH NEXT FROM sql_cursor   
           INTO @tbl_name,
                @object_id;
         END
         CLOSE sql_cursor;
         DEALLOCATE sql_cursor;
     END
     GO

    Luego ejecutamos el siguiente código:

     CREATE TABLE #MyLocalTempTable1 ([ID] INT);
     CREATE TABLE #MyLocalTempTable2 ([ID] INT);
    
     SELECT [t].[name], [t].[object_id]
     FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
     WHERE [t].[name] LIKE '#[^#]%'
     AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
     AND [t].[type] = 'U';
    
     EXEC [dbo].[DropSessionLocalTempTables];
    
     SELECT [t].[name], [t].[object_id]
     FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
     WHERE [t].[name] LIKE '#[^#]%'
     AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
     AND [t].[type] = 'U';

    Los resultados muestran que se han eliminado todas las tablas temporales locales. Esto se debe a que no especificamos el parámetro de entrada @table_name:

    Sin embargo, si establecemos @nombre de la tabla parámetro, eliminará solo esta tabla:

     CREATE TABLE #MyLocalTempTable1 ([ID] INT);
     CREATE TABLE #MyLocalTempTable2 ([ID] INT);
    
     SELECT [t].[name], [t].[object_id]
     FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
     WHERE [t].[name] LIKE '#[^#]%'
     AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
     AND [t].[type] = 'U';
    
     EXEC [dbo].[DropSessionLocalTempTables] @table_name=N'#MyLocalTempTable2';
    
     SELECT [t].[name], [t].[object_id]
     FROM [tempdb].[sys].[tables] as [t] WITH (READUNCOMMITTED)
     WHERE [t].[name] LIKE '#[^#]%'
     AND OBJECT_ID(N'tempdb..' + QUOTENAME([t].[name])) IS NOT NULL
     AND [t].[type] = 'U';

    En nuestro caso, eliminó una tabla temporal local específica.

    Nota: Usamos la herramienta SQL Complete para formatear el código.

    El procedimiento guardado de eliminación de horarios locales se implementó como [srv].[DropSessionLocalTempTables] y SRV base de datos.

    Conclusión

    Revisamos y comparamos tablas de servidores SQL temporales locales y globales, así como tablas de variables. También creamos un script, lo envolvimos en un procedimiento almacenado y lo usamos para eliminar todas las tablas temporales locales o una tabla temporal local específica. Esperamos que encuentres útiles todas estas ideas.

    Literatura

    1. documentación del servidor SQL
    2. Consejos
    3. SSMS
    4. Depurador para SSMS
    5. dbForge Studio para SQL Server
    6. herramientas SQL
    7. Depurador de SQL en dbForge Studio para SQL Server

    Artículos de interés

    Subir