Delete All Procedures from a database in SQL Server

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.

SELECT    

 ‘DROP PROCEDURE ‘ +SCHEMA_NAME(schema_id)+‘.’+name+”

  as ‘CopyThisColumnAndExecuteToDeleteAllProcedures

  FROM     sys.procedures 

 WHERE      [type] = ‘P‘      AND  is_ms_shipped = 0

AND [name] NOT LIKE ‘sp[_]%diagram%

ORDER BY       [name] ASC

Advertisements

SQL best practices for better performance

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.

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/