Sql server to drop all stored procedures

How to drop all the stored procedure of given database in sql server? Here we will write the sql server script or queries for different situations.

Drop all stored procedures in the current database:

DECLARE @vcDropQuery AS VARCHAR(MAX)

SELECT

     @vcDropQuery = ISNULL(@vcDropQuery + ';','')

          + 'DROP PROCEDURE '

          + QUOTENAME(ROUTINE_SCHEMA)

          + '.' + QUOTENAME(ROUTINE_NAME )

FROM INFORMATION_SCHEMA.ROUTINES

EXECUTE(@vcDropQuery)

Drop all stored procedures in the current database with given prefix:

DECLARE @vcPrefix AS VARCHAR(100) = 'usp'

DECLARE @vcDropQuery AS VARCHAR(MAX)

SELECT

     @vcDropQuery = ISNULL(@vcDropQuery + ';','')

          + 'DROP PROCEDURE '

          + QUOTENAME(ROUTINE_SCHEMA)

          + '.' + QUOTENAME(ROUTINE_NAME )

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME LIKE  @vcPrefix + '%'

EXECUTE(@vcDropQuery)

Drop all stored procedures in the current database with contains specific text:

DECLARE @vcContiansText AS VARCHAR(100) = 'import'

DECLARE @vcDropQuery AS VARCHAR(MAX)

SELECT

     @vcDropQuery = ISNULL(@vcDropQuery + ';','')

          + 'DROP PROCEDURE '

          + QUOTENAME(ROUTINE_SCHEMA)

          + '.' + QUOTENAME(ROUTINE_NAME )

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME LIKE  '%' + @vcContiansText + '%'

EXECUTE(@vcDropQuery)

Drop all stored procedures in the current database under given schema:

DECLARE @vcSchemaName AS VARCHAR(100) = 'dbo'

DECLARE @vcDropQuery AS VARCHAR(MAX)

SELECT

     @vcDropQuery = ISNULL(@vcDropQuery + ';','')

          + 'DROP PROCEDURE '

          + QUOTENAME(ROUTINE_SCHEMA)

          + '.' + QUOTENAME(ROUTINE_NAME )

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_SCHEMA = @vcSchemaName

EXECUTE(@vcDropQuery)

Drop all stored procedures in the current database which has created today:

DECLARE @vcDropQuery AS VARCHAR(MAX)

SELECT

     @vcDropQuery = ISNULL(@vcDropQuery + ';','')

          + 'DROP PROCEDURE '

          + QUOTENAME(ROUTINE_SCHEMA)

          + '.' + QUOTENAME(ROUTINE_NAME )

FROM INFORMATION_SCHEMA.ROUTINES

WHERE CAST(CREATED AS DATE) = CAST(GETDATE() AS DATE)

EXECUTE(@vcDropQuery)

Drop all stored procedures in the current database which has modified today:

DECLARE @vcDropQuery AS VARCHAR(MAX)

SELECT

     @vcDropQuery = ISNULL(@vcDropQuery + ';','')

          + 'DROP PROCEDURE '

          + QUOTENAME(ROUTINE_SCHEMA)

          + '.' + QUOTENAME(ROUTINE_NAME )

FROM INFORMATION_SCHEMA.ROUTINES

WHERE CAST(LAST_ALTERED AS DATE) = CAST(GETDATE() AS DATE)

EXECUTE(@vcDropQuery)

Drop all stored procedures in the all given databases:

CREATE TABLE #tblDbList(vcDbName VARCHAR(200))

--Insert the database names for which do you want to drop all stored procedures

INSERT INTO #tblDbList

VALUES('Exact_Help'),('School')

DECLARE @vcDropQuery AS VARCHAR(MAX)

DECLARE @tblTable TABLE(vcDropQuey VARCHAR(MAX))

INSERT @tblTable

EXECUTE SP_EXECUTESQL  N'SP_MSFOREACHDB ''SELECT

          ''''DROP PROCEDURE ''''

          + QUOTENAME(ROUTINE_CATALOG )

          + ''''.'''' + QUOTENAME(ROUTINE_SCHEMA)

          + ''''.'''' + QUOTENAME(ROUTINE_NAME )

FROM  [?].INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME NOT LIKE ''''SP%''''

     AND ''''?'''' IN (SELECT * FROM #tblDbList) '''

SELECT

     @vcDropQuery = ISNULL(@vcDropQuery + ';','') +  vcDropQuey

FROM @tblTable

EXECUTE(@vcDropQuery)