Find Stored procedure which are using temp tables

To write query to search through all procedures, and list procedures which uses temp tables.

SQL Server doesn't store any type of metadata or dependency information about temp tables potentially created inside of them


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 (LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%create%table%#%'
OR LOWER(OBJECT_DEFINITION(p.[object_id])) LIKE N'%select%into%#%')
and p.name not like 'z%' 
and p.name not like '%old'
order by p.name