日期:2011-01-25 23:11:00 来源:本站整理
<b>sql server批量改正表和存储历程的办法</b>[MSSQL防范]
本文“<b>sql server批量改正表和存储历程的办法</b>[MSSQL防范]”是由七道奇为您精心收集,来源于网络转载,文章版权归文章作者所有,本站不对其观点以及内容做任何评价,请读者自行判断,以下是其具体内容:
在我们利用SQL数据库的历程中,有时需求用到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要先退出来,再进去,才能看到更改后的后果.
以上是“<b>sql server批量改正表和存储历程的办法</b>[MSSQL防范]”的内容,如果你对以上该文章内容感兴趣,你可以看看七道奇为您推荐以下文章:
本文地址: | 与您的QQ/BBS好友分享! |
评论内容只代表网友观点,与本站立场无关!
评论摘要(共 0 条,得分 0 分,平均 0 分)
查看完整评论