关于SQL数据表存储过程表名前缀换成dbo代码

来源：互联网发布：苏联156项工程知乎编辑：程序博客网时间：2024/06/12 20:26

1.全部表名换成dbo

<span style="font-size:18px;">EXEC sp_MSforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' '</span>

2.存储过程全部换成dbo

<span style="font-size:18px;">CREATE PROCEDURE dbo.ChangeObjectOwner @OldOwner as NVARCHAR(128)='dbo',--参数原所有者 @NewOwner as NVARCHAR(128)='dbo'--参数新所有者 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 FETCH NEXT FROM curObject INTO @Name, @Owner END close curObject deallocate curObjectGO </span>

然后在运行

<span style="font-size:18px;">exec ChangeObjectOwner '数据库','dbo' </span>

1 0