SQL Server - find SPs that don't drop temp tables
(1) Is there a good / reliable way to query the system catalog in order to
find all the stored procedures that create temporary tables in their
source bodies, but which don't drop them at the end of their bodies ?
(2) In general, can creating temporary tables in SP and not dropping them in the same SP cause some problems, and if so, what problems?
I am asking this question in the context of SQL Server 2008 R2 and SQL Server 2012 mostly.
Thank you very much in advance.
source to share
1) there is probably no good / reliable way - although you can extract sp text using some arcane ways you may find elsewhere.
2) In general - no, this is not a problem - temporary tables (#tables) are scoped and will be marked for deletion when their scope is gone.
and table variables similarly
The exception is global temporary tables (## tables), which are cleared when the scope contains no references to them. Avoid these guys - usually (almost always) read up on better ways to do things than with a global temp table.
Take a breath - if you want to go down the path (1) then be aware that there are many pitfalls when looking at the code inside the sql server - many helper functions and info tables truncate the actual code to NVARCHAR (4000)
If you look at the sp_helptext code, you will see a really horrible cursor that will pull out the actual text.
I wrote this a long time ago to search for strings in code - you can run it in 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 selection tool you like on your codebase - you have all your sp and so on, the scripts of which are somewhere in the source code, right .....?
source to share
Not 100% sure this is accurate, as I don't have a good test case 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
Then use a function like this. It looks for procedure text for lines and reports when the creation count does not match the blob count:
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
source to share