Drop all SqlQueryNotificationService from Database

DECLARE @PROCNAME VARCHAR(500)
DECLARE CUR CURSOR 

FOR SELECT [NAME] FROM SYS.OBJECTS 

    WHERE TYPE IN (N'SQ') AND NAME LIKE 'SQLQUERYNOTIFICATIONSERVICE%'
OPEN CUR
FETCH NEXT FROM CUR INTO @PROCNAME
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC ('DROP QUEUE [' + @PROCNAME+']')
        FETCH NEXT FROM CUR INTO @PROCNAME
    END TRY
    BEGIN CATCH
        FETCH NEXT FROM CUR INTO @PROCNAME
    END CATCH
END
CLOSE CUR
DEALLOCATE CUR

Drop all SqlQueryNotificationStoredProcedure from Database

below query will drop all SqlQueryNotificationStoredProcedure available in specific database.

ALTER AUTHORIZATION ON DATABASE :: <Your Database Name> TO <[login user]>

for example your DB name : TestDB and sql server login user: sa, then you can write
ALTER AUTHORIZATION ON DATABASE :: TestDB TO [sa]

Example :

ALTER AUTHORIZATION ON DATABASE :: TestDB  TO [sa]
DECLARE @PROCNAME VARCHAR(500)
DECLARE CUR CURSOR 
FOR SELECT [NAME] FROM SYS.OBJECTS WHERE TYPE IN (N'P', N'PC') AND NAME LIKE 'SQLQUERYNOTIFICATIONSTOREDPROCEDURE%'

OPEN CUR
FETCH NEXT FROM CUR INTO @PROCNAME

WHILE @@FETCH_STATUS = 0
BEGIN

  EXEC ('DROP PROCEDURE [' + @PROCNAME+']')
  FETCH NEXT FROM CUR INTO @PROCNAME

END
CLOSE CUR
DEALLOCATE CUR

Sql Query for generate script of all stored procedure in Database

In this post will show you, how to generate script that lists all stored procedure in single file.

below is the query for that :

Method 1 :

DECLARE @MYSP AS TABLE(ID INT IDENTITY(1,1), SPNAME VARCHAR(255),SPDESC VARCHAR(MAX))
INSERT INTO @MYSP 
SELECT ROUTINE_NAME,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_TYPE='PROCEDURE' order by ROUTINE_NAME

DECLARE @MIN INT
DECLARE @MAX INT
SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @MYSP

WHILE @MIN <= @MAX
BEGIN
    DECLARE @sp_desc varchar(max)
    SELECT @sp_desc=SPDESC FROM @MYSP WHERE ID=@MIN
    
    print @sp_desc
    
    SET @MIN=@MIN+1
END

you can get list of all stored procedures from Information_Schema.Routines tables. after that you can loop each record and print SP.


Method 2 :

 DECLARE @MYSP AS TABLE(ID INT IDENTITY(1,1), SPNAME VARCHAR(255))

 INSERT INTO @MYSP SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
 WHERE  ROUTINE_TYPE='PROCEDURE' order by ROUTINE_NAME

 DECLARE @MIN INT
 DECLARE @MAX INT

 SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @MYSP

 declare @text nvarchar(max)

 WHILE @MIN <= @MAX

 BEGIN

    DECLARE @sp_name varchar(255)
    SELECT @sp_name=SPNAME FROM @MYSP WHERE ID=@MIN
    EXEC SP_HELPTEXT @sp_name    

    SET @MIN=@MIN+1

 END