Batch delete stored procedures and batch modify stored procedures

Batch delete stored procedures and batch modify stored procedures
Modify:

Declare proccur cursor
For
Select [name] from sysobjects where name like 'Foods _ %'
Declare @ procname varchar (100)
Declare @ temp varchar (100)
Open proccur
Fetch next from proccur into @ procname
While (@ FETCH_STATUS = 0)
Begin
Set @ temp = 'kcb _ '+ @ procname
EXEC SP_RENAME @ procname, @ temp
  
Print (@ procname + 'deleted ')
Fetch next from proccur into @ procname
End
Close proccur
Deallocate proccur

Detailed instance

Modify the table owner in batches:

EXEC sp_MSforeachtable 'exec sp_changeobjectowner ''? '', ''Dbo '''
Modify the table owner:
Exec sp_changeobjectowner 'name of the table to be changed ', 'dbo'

Modify the stored procedure in batches:

Create procedure ChangeProcOwner @ OldOwner as NVARCHAR (128), -- Parameter original owner @ NewOwner as NVARCHAR (128) -- Parameter new owner as declare @ Name as NVARCHAR (128) DECLARE @ Owner as NVARCHAR (128) DECLARE @ OwnerName as NVARCHAR (128) DECLARE curObject cursor for select 'name' = Name, 'owner' = user_name (uid) from sysobjects where user_name (uid) = @ OldOwner and xtype = 'p' order by name OPEN curObject fetch next from curObject INTO @ Name, @ Owner WHILE (@ FETCH_STATUS = 0) BEGIN if @ Owner = @ OldOwner begin set @ OwnerName = @ OldOwner + '. '+ rtrim (@ Name) exec sp_changeobjectowner @ OwnerName, @ NewOwner end fetch next from curObject INTO @ Name, @ Owner END close curObject deallocate curObject GO execute exec ChangeProcOwner 'XX ', 'dbo'
Or
Exec ChangeProcOwner '? ', 'Dbo'

Another method is as follows:

-- If a database tutorial (table and stored procedure) has multiple user names, you can use the following statement to change all of them to dbo:

Select 'sp _ changeobjectowner ''' + User_Name (Uid) + '. '+ name + ''', ''dbo'' From sysobjects Where Uid Not in (User_ID ('dbo') And Type In ('u', 'P ')

Delete:

First look at the deleted

Sqlserver 2005 can only delete one stored procedure at a time. If there is more than one stored procedure, it takes a long time to delete it. Therefore, a statement is written, simply drop all user-defined stored procedures in the database. However, please pay attention to the database currently opened. Post code below

Declare proccur cursor
For
Select [name] from sysobjects where type = 'p'
Declare @ procname varchar (100)
Open proccur
Fetch next from proccur into @ procname
While (@ FETCH_STATUS = 0)
Begin
-- Exec ('drop proc' + @ procname) -- This sentence is commented out, please cancel

Print (@ procname + 'deleted ')
Fetch next from proccur into @ procname
End
Close proccur
Deallocate proccur

 

Let's look at the batch delete stored procedure.

Declare proccur cursor
For
Select [name] from sysobjects where name like 'users _ %'
Declare @ procname varchar (100)
Open proccur
Fetch next from proccur into @ procname
While (@ FETCH_STATUS = 0)
Begin
Exec ('drop proc' + @ procname)
Print (@ procname + 'deleted ')
Fetch next from proccur into @ procname
End
Close proccur
Deallocate proccur