葡京网投哪个正规 > 新葡亰-编程 > 成批更改sql,更改数据库中表的所属用户的两个方法

原标题:成批更改sql,更改数据库中表的所属用户的两个方法

浏览次数:95 时间:2020-03-25

核心提示:因为一些网站是使用虚拟主机.用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

葡京正网网投 1葡京正网网投 2Code
CREATE PROCEDURE dbo.ChangeObjectOwner
@CurrentOwner as NVARCHAR(128),--当前所有者
@NewOwner as NVARCHAR(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 NVARCHAR(128),--参数原所有者 @NewOwner as NVARCHAR(128)--参数新所有者 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_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 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 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 2005一次只能删除一个存储过程,如果多了,需要很长时间才能删完,所以写了一段语句,直接就把当然数据库下所有用户自定义的存储过程给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 = 0)begin --exec('drop proc ' + @procname) --本句被注释,使用时请取消

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

本文由葡京网投哪个正规发布于新葡亰-编程,转载请注明出处:成批更改sql,更改数据库中表的所属用户的两个方法

关键词:

上一篇:基本语法,实用工具葡京网投哪个正规

下一篇:Server面试题整合,多表查询