SQL Server - find SPs which don't drop temp tables

Question

(1) Is there a good/reliable way to query the system catalogue in order
to find all stored procedures which create some temporary tables in their
source code bodies but which don't drop them at the end of their bodies?

(2) In general, can creating temp tables in a SP and not dropping
them in the same SP cause some problems and if so, what problems?

I am asking this question in the contexts of
SQL Server 2008 R2 and SQL Server 2012 mostly.

Many thanks in advance.

Answer 1

Not 100% sure if this is accurate as I don't have a good set of test data to work with. First you need a function to count occurrences of a string (shamelessly stolen from here):

CREATE FUNCTION dbo.CountOccurancesOfString
(
    @searchString nvarchar(max),
    @searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END

Next make use of the function like this. It searches the procedure text for the strings and reports when the number of creates doesn't match the number of drops:

WITH CreatesAndDrops AS (
SELECT  procedures.name,
        dbo.CountOccurancesOfString(UPPER(syscomments.text), 'CREATE TABLE #') AS Creates,
        dbo.CountOccurancesOfString(UPPER(syscomments.text), 'DROP TABLE #') AS Drops
FROM sys.procedures
JOIN sys.syscomments
    ON procedures.object_id = syscomments.id
    )

SELECT * FROM CreatesAndDrops
WHERE Creates <> Drops

Answer 2

1) probably no good / reliable way -- though you can extract the text of sp's using some arcane ways that you can find in other places.

2) In general - no this causes no problems -- temp tables (#tables) are scope limited and will be flagged for removal when their scope disappears.

and table variables likewise

an exception is for global temp tables (##tables) which are cleaned up when no scope holds a reference to them. Avoid those guys -- there are usually (read almost always) better ways to do something than with a global temp table.

Sigh -- if you want to go down the (1) path then be aware that there are lots of pitfalls in looking at code inside sql server -- many of the helper functions and information tables will truncate the actual code down to a NVARCHAR(4000)

If you look at the code of sp_helptext you'll see a really horrible cursor that pulls the actual text..

I wrote this a long time ago to look for strings in code - you could run it on your database -- look for 'CREATE TABLE #' and 'DROP TABLE #' and compare the outputs....

DECLARE @SearchString VARCHAR(255) = 'DELETE FROM'

SELECT
    [ObjectName]
    , [ObjectText]
FROM
    (
    SELECT
        so.[name] AS [ObjectName]
        , REPLACE(comments.[c], '#x0D;', '') AS [ObjectText]
    FROM
        sys.objects AS so
        CROSS APPLY ( 
            SELECT CAST([text] AS NVARCHAR(MAX))
            FROM syscomments AS sc
            WHERE sc.[id] = so.[object_id]
            FOR XML PATH('')
            )
            AS comments ([c])
    WHERE
        so.[is_ms_shipped] = 0
        AND so.[type] = 'P'
    )
    AS spText
WHERE
    spText.[ObjectText] LIKE '%' + @SearchString + '%'

Or much better - use whatever tool of choice you like on your codebase - you've got all your sp's etc scripted out into source control somewhere, right.....?