在我们使用SQL数据库的过程中,有时需要用到sql server批量修改,接下来的SQL server内容就将为您介绍sql server批量修改表和存储过程的方法,希望对您学习sql server批量修改能有所帮助。
批量修改表的所有者:
EXEC sp_MSforeachtable 'exec sp_changeobjectowner '' '',''dbo'' '
单个修改表所有者:
exec sp_changeobjectowner '要改的表名','dbo'
批量修改存储过程的存储过程:
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'
还有一种方法:
--假如一个数据库中(表和存储过程)有多个用户名,而要把它所有都改成dbo 就可以用如下的语句
Select 'sp_changeobjectowner ''' + User_Name(Uid) + '.' + name + ''',''dbo'' ' From sysobjects Where Uid Not in (User_ID('dbo')) And Type In ('U','P')
--然后把查询出来的语句拷贝出来,直接运行就表和存储过程的对象都会改过来,但SQL SERVER要先退出来,再进去,才能看到更改后的结果。