How to get a list of temporary tables created in a stored procedure?

Question

I have a stored procedure where I create several temporary tables. How can I get the list of those temporary tables created in that stored procedure?

Something like this:

SELECT [# temporary table name]
FROM sys.procedures
WHERE name = '<Stored Procedure Name>'

I want this result

Temporary_Table_Name
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#TemporaryTable1
#TemporaryTable2
.
.
.
#TemporaryTableN

(N row(s) affected)

Then, with that list, I want to built DROP TABLE instructions dynamically.  

    Dinamic_DROP_Instruction
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#TemporaryTable1') IS NOT NULL DROP TABLE #TemporaryTable1
IF OBJECT_ID('tempdb..#TemporaryTable2') IS NOT NULL DROP TABLE #TemporaryTable1
.
.
.
IF OBJECT_ID('tempdb..#TemporaryTableN') IS NOT NULL DROP TABLE #TemporaryTableN

(N row(s) affected)

Answer 1

I was able to construct a code to get the list of temporary tables and also set up the dynamic instruction to DROP each temporary table if it exists.

I leave the code and the links of the sources on which I was based.

CODE:

DECLARE @NameStoreProcedure AS VARCHAR(100) = 'Name_of_store_procedure' --Do not place the scheme

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
FROM #TemporalTableNames

/*
--Dynamic Instruction for DROP instructios
    SELECT 'IF OBJECT_ID(''tempdb..' + NAME + ''') IS NOT NULL DROP TABLE ' + NAME
    FROM #TemporalTableNames
*/

Answer 2

Too long to comment....

This isn't going to be easy and will need a parse function likely. To start, use OBJECT_DEFINITION or sp_helptext or look in sys.sql_modules or what ever other method you want to get the definition. Then you'll have to search for your temp table based on # or what ever other method you want, and split those. It's going to be extremely messy and error prone IMHO. Here's a start.

 SELECT 
    SUBSTRING(
            OBJECT_DEFINITION(OBJECT_ID('yourProcedure')),
            CHARINDEX('#',OBJECT_DEFINITION(OBJECT_ID('yourProcedure'))),
            60)

60 here is just a made up number. You'd want to find the first white space after the # or something similar. Again, I don't think there is a fast way other than using CONTROL+F in your procedure and manually adding the DROPstatements...