Feb 7, 2013

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)

No comments:

Post a Comment