Get a list of temporary tables created in a stored procedure

GetListOfTempTables.sql

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
*/

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.

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.