Skip to content

Instantly share code, notes, and snippets.

@jotapardo
Last active January 5, 2017 13:54
Show Gist options
  • Save jotapardo/63d85ed75b8a48467320af8af16d2f8b to your computer and use it in GitHub Desktop.
Save jotapardo/63d85ed75b8a48467320af8af16d2f8b to your computer and use it in GitHub Desktop.
Get a list of temporary tables created in a stored procedure
DECLARE @NameStoreProcedure AS VARCHAR(100) = 'Name_of_store_procedure' --Do not place the scheme
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = OBJECT_ID(@NameStoreProcedure))
BEGIN
SELECT 'Invalid store procedure name ' + @NameStoreProcedure
RETURN
END
IF OBJECT_ID('tempdb..#Positions') IS NOT NULL
DROP TABLE #Positions
IF OBJECT_ID('tempdb..#TemporalTableNames') IS NOT NULL
DROP TABLE #TemporalTableNames
--Find all positions: http://dba.stackexchange.com/questions/41961/how-to-find-all-positions-of-a-string-within-another-string
DECLARE @term CHAR(20) = 'create'
DECLARE @string VARCHAR(MAX)
SELECT @string = OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE NAME = @NameStoreProcedure
SET @string += '.' --Add any data here (different from the one searched) to get the position of the last character
------------------------------------------------------------------------------------------------------------------------
--Range of numbers: http://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers-in-sql-server
DECLARE @min BIGINT
, @max BIGINT
SELECT @Min = 1
, @Max = len(@string)
------------------------------------------------------------------------------------------------------------------------
--Get positions of 'CREATE'
SELECT pos = Number - LEN(@term)
INTO #Positions
FROM (
SELECT Number
, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, CHARINDEX(@term, @string + @term, Number) - Number)))
FROM (
SELECT TOP (@Max - @Min + 1) @Min - 1 + row_number() OVER (
ORDER BY t1.number
) AS N
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) AS n(Number)
WHERE Number > 1
AND Number <= CONVERT(INT, LEN(@string))
AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
) AS y
SELECT RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(substring(@string, pos - 1, CHARINDEX('(', @string, pos) - pos + 1), CHAR(9), ''), CHAR(13), ''), CHAR(10), ''))) AS NAME
INTO #TemporalTableNames
FROM #Positions
WHERE substring(@string, pos - 1, CHARINDEX('(', @string, pos) - pos + 1) LIKE '#%'
--List of temporary tables
SELECT NAME AS Temporary_Table_Name
FROM #TemporalTableNames
/*
--Dynamic Instruction for DROP instructios
SELECT 'IF OBJECT_ID(''tempdb..' + NAME + ''') IS NOT NULL DROP TABLE ' + NAME AS Dinamic_DROP_Instruction
FROM #TemporalTableNames
*/
@jotapardo
Copy link
Author

jotapardo commented Jan 4, 2017

Aclaración:

Este script lo que hace es consultar dentro de un procedimiento almacenado específico (El que se asigne en la línea 1: DECLARE @NameStoreProcedure AS VARCHAR(100) = 'Name_of_store_procedure' --Do not place the scheme), una lista de las tablas temporales creadas internamente. No borra tablas, sino únicamente devuelve el listado. Y adicionalmente otro listado con las instrucciones IF OBJECT_ID('tempdb..#tpm') IS NOT NULL DROP TABLE ..#tpm

Lo que realiza el script es buscar dentro de la definición del procedimiento los CREATE de tablas temporales. No consulta tablas temporales activas y es independiente a si se ejecuta o no el procedimiento.

Si el procedimiento está cifrado no funciona.

Esto es útil para el área de soporte cuando desea convertir un SP a un script ( lo que se hace normalmente cambiando la parte del ALTER del procedimiento por un DECLARE) y al ejecutar este script obtendría los DROP de las tablas temporales y no tener que estar ejecutando y darse cuenta que ya existe una tabla temporal y la debe borrar.

@jotapardo
Copy link
Author

Clarification:

This script is to query within a specific stored procedure (The one assigned in line 1: DECLARE @NameStoreProcedure AS VARCHAR (100) = 'Name_of_store_procedure' --Do not place the scheme), a list of temporary tables Created internally. It does not erase tables, but only returns the list. And additionally another listing with the instructions IF OBJECT_ID ('tempdb .. # tpm') IS NOT NULL DROP TABLE .. # tpm

What the script performs is to search within the definition of the procedure the CREATE of temporary tables. It does not query active temporary tables and is independent of whether or not the procedure is executed.

If the procedure is encrypted it does not work.

This is useful for the support area when you want to convert an SP to a script (which is usually done by changing the ALTER part of the procedure by a DECLARE) and when executing this script you would get the DROPs from the temporary tables and not have to be Executing and realizing that a temporary table already exists and must be deleted.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment