Sometime we need to delete the all the stored procedures in particular database .For this use the below script to generate to drop the all the store procedures. After execute this query it will gives script for drop store procedures with schema name.
‘DROP PROCEDURE ‘ +SCHEMA_NAME(schema_id)+‘.’+name+”
WHERE [type] = ‘P‘ AND is_ms_shipped = 0
AND [name] NOT LIKE ‘sp[_]%diagram%‘
ORDER BY [name] ASC
In general while developing the queries the required result will retrieve from query. But the performance will be down after some days in production server due to data .In the scenario we have optimize the queries. So while developing the sql queries, stored procedures we follow some techniques for better performance. Below links for some of the practices sql queries.