批量删除存储过程和批量修改存储过程

主旨提示:因为有个别网址是使用设想主机.用sql-server导数据上去的话

–校订某些表
exec sp_changeobjectowner ‘tablename’,’dbo’
–存款和储蓄改过全体表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
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
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
批量删除存储过程和批量修改存储过程。– select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO

批量去除存款和储蓄进度和批量改换存款和储蓄进度 修改:

 

因为有个别网址是行使虚构主机.用sql-server导数据上去的话.
数据库的客户暗中同意是您登入的客户,不是dbo,有的时候就和本地爆发差别.

declare proccur cursor for select [name] from sysobjects where name
like ‘Foods_%’declare @procname varchar(100)declare @temp
varchar(100)open proccurfetch next from proccur into
@procnamewhile(@@FETCH_STATUS = 0)begin set @temp=’kcb_’+@procname
EXEC SP_RENAME @procname,@temp print(@procname + ‘已被删除’卡塔尔 fetch
next from proccur into @procnameendclose proccurdeallocate proccur

www.9778.com 1www.9778.com 2Code
CREATE PROCEDURE dbo.ChangeObjectOwner
@CurrentOwner as NVARCHAENVISION(128卡塔尔国,–当前主人
@NewOwner as NVARCHA路虎极光(128卡塔尔(قطر‎–新全体者
AS 
DECLARE @Name   as NVARCHAR(128) 
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128) 

–功能说明:成批改正数据库全数者的靶子 –笔者:不详 –用法:exec
ChangeObjectOwner nmkspro,dbo –就能够将有所nmkspro全部者的对象改为dbo所有–运维成功后将唤起:注意: 纠正对象名的任一部分都也许损坏脚本和存款和储蓄进度。
CREATE PROCEDURE dbo.ChangeObjectOwner @OldOwner as
NVARCHA瑞鹰(128State of Qatar,–参数原主人 @NewOwner as NVARCHA普拉多(128State of Qatar–参数新全部者 AS

详见实例

DECLARE curObject CURSOR FOR 
select ‘Name’   = name,
‘Owner’   = user_name(uid)
from sysobjects
where user_name(uid)=@CurrentOwner  –and   xtype=’p’
order by name 

DECLARE @Name as NVARCHAR(128) DECLARE @Owner as NVARCHAR(128) DECLARE
@OwnerName as NVARCHAR(128)

批量校勘表的主人:

OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN   
if @Owner=@CurrentOwner 
begin
set @OwnerName = @CurrentOwner + ‘.’ + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end  

DECLARE curObject CURSOR FOR select Name = name, Owner = user_name(uid)
from sysobjects where user_name(uid)=@OldOwner order by name

EXEC sp_www.9778.com,MSforeachtable ‘exec sp_changeobjectowner ”?”,”dbo” ‘
单个修正表全部者: exec sp_changeobjectowner ‘要改的表名’,’dbo’

FETCH NEXT FROM curObject INTO @Name, @Owner
END 

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

批量改变存储进程的蕴藏进程:

close curObject
deallocate curObject

FETCH NEXT FROM curObject INTO @Name, @Owner END

CREATE PROCEDURE ChangeProcOwner @OldOwner as
NVARCHA本田UR-V(128卡塔尔,–参数原主人 @NewOwner as NVARCHAEscort(128卡塔尔(قطر‎–参数新全部者
AS DECLARE @Name as NVARCHAOdyssey(128卡塔尔(قطر‎ DECLARE @Owner as NVARCHATiggo(128卡塔尔国DECLARE @OwnerName as NVARCHA奇骏(128State of Qatar DECLARE curObject CU景逸SUVSOEscort FO牧马人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 执行 exec ChangeProcOwner ‘xx’,’dbo’ 或者exec
ChangeProcOwner ‘?’,’dbo’

 

close curObject deallocate curObject GO

还会有一种艺术:

批量修改:
EXEC sp_MSforeachtable ‘exec sp_changeobjectowner ”?”,”dbo” ‘
单个更改:
EXEC sp_changeobjectowner ‘要改的表名’,’dbo’
假使想纠正存款和储蓄进度Owner:
先把–and   xtype=’p’
反注释,再创建sp_changeobjectowner 存款和储蓄进程。
然后实践:EXEC sp_changeobjectowner ‘CurrentOwner’,’dbo’ 

批量改善: EXEC sp_MSforeachtable exec sp_changeobjectowner ?,dbo
单个修正: exec sp_changeobjectowner 要改的表名,dbo

–倘诺三个数据库教程中(表和存款和储蓄进程卡塔尔国有多少个客商名,而要把它具备都改成dbo
就足以用如下的话语

 

方法一:间接点击该表-》设计-》属性-》改过上边的持有者。该方法针对于表少的动静下。
方法二:利用脚本直接实行,用系统帐号恐怕超户登录到该数据库,然后履行下边语句:
sp_configure allow updates,1 go reconfigure with override go update
sysobjects set uid=1 where uid1
–依据标准能够随意校订,在多顾客之间切换。 go sp_configure allow
updates,0 go reconfigure with override

Select ‘sp_changeobjectowner ”’ + User_Name(Uid) + ‘.’ + name +
”’,”dbo” ‘ From sysobjects Where Uid Not in (User_ID(‘dbo’)) And
Type In (‘U’,’P’)

删除:

先看单删除的

sqlserver
贰零零柒二回只可以删除一个储存进程,纵然多了,须要不长日子本领删完,所以写了一段语句,直接就把自然数据库下具备客户自定义的积攒进度给drop了。不过使用都请在乎,当前开辟的数据库哦。上面贴代码

declare proccur cursor for select [name] from sysobjects where
type=’P’declare @procname varchar(100)open proccurfetch next from
proccur into @procnamewhile(@@FETCH_STATUS = 0State of Qatarbegin –exec(‘drop
proc ‘ + @procnameState of Qatar –本句被疏解,使用时请撤除

print(@procname + ‘已被删除’卡塔尔国 fetch next from proccur into
@procnameendclose proccurdeallocate proccur

再看批量刨除存储进程

declare proccur cursor for select [name] from sysobjects where name
like ‘Users_%’ declare @procname varchar(100)open proccurfetch next
from proccur into @procnamewhile(@@FETCH_STATUS = 0卡塔尔begin exec(‘drop
proc ‘ + @procname卡塔尔国 print(@procname + ‘已被删除’卡塔尔 fetch next from
proccur into @procnameendclose proccurdeallocate proccur