Published on: 22 Dec, 2020

How to delete all user creation stored procedure in TSQL?

Tumblr Digg Google Plus

If you need to delete all the user creation stored procedure in SQL server database. You can use the following TSQL code snippet.


DECLARE @spName VARCHAR(128)
DECLARE @SQL VARCHAR(500)

SELECT @spName = (
	SELECT TOP 1 [name] 
	FROM sysobjects 
	WHERE [type] = 'P' 
	AND category = 0 
	ORDER BY [name]
)

WHILE @spName is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@spName) +']'
    EXEC (@SQL)
    PRINT 'Deleted Stored Procedure: ' + @spName
    SELECT @spName = ((
		SELECT TOP 1 [name] 
		FROM sysobjects 
		WHERE [type] = 'P' 
		AND category = 0 
		ORDER BY [name]
	)
END
GO

The sysobjects table contains all the non-system stored procedures created by user. We can easily identify the record by specifying the type which has a type of P that indicate it is a stored procedure type.

 

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.