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

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.