Find Stored procedure which are using temp tables [duplicate]

Posted on

Question :

We have got nearly 700 plus stored procedures with nearly 50% uses #tmp tables in their queries.

Need to write query to search through all procedures, and list procedures which uses tmp tables.

Can this be done by querying some system tables where procedure information is stored.

Answer :

Unfortunately, SQL Server doesn’t store any type of metadata or dependency information about temp tables potentially created inside of them, so you’ll have to parse. A first pass could be:

SELECT [schema] =, 
  [procedure] = 
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
ON p.[schema_id] = s.[schema_id]

You’ll get some false positives, probably. You can tighten down the search space with various approaches, like:

LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%create%table%#%';
OR LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%select%into%#%';

But those things could still appear in a comment or in multiple completely unrelated statements.

Leave a Reply

Your email address will not be published. Required fields are marked *