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