CODEWITHSUNDEEP
sql-serverwhile-loop
WagoL
WagoL

Reputation: 107

Use select statement results in while loop

I have this query:

SELECT name FROM SomeDB.sys.objects WHERE type IN ('P') AND name LIKE('dbo%');

What I want to do now is use the name I selected from the table in a while loop. Like this (pseudo code)

WHILE (select < select.END)
BEGIN  
   DROP PROC select.name 
END

How can I achieve this?

Upvotes: 1

Views: 74

Answers (1)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

For dropping database objects you can use dynamic script like below. If any dependencies are exist then you need to drop parent first.

DECLARE @SQL NVARCHAR(MAX)

SET @SQL=(
SELECT 'DROP PROCEDURE '+NAME+';' FROM SYS.OBJECTS WHERE TYPE IN ('P') 
FOR XML PATH('') )
--select @sql
EXEC SP_EXECUTESQL @SQL

Upvotes: 1

Related Questions