Find Stored procedure which are using temp tables [duplicate]

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.

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] = s.name, 
  [procedure] = p.name 
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
ON p.[schema_id] = s.[schema_id]
WHERE OBJECT_DEFINITION(p.[object_id]) LIKE N'%#%';

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.