IF OBJECT_ID('ChangeAll') IS NOT NULL DROP PROC ChangeAll GO CREATE PROCEDURE ChangeAll ( @old sysname, @new sysname ) AS DECLARE @objname sysname SET NOCOUNT ON IF USER_ID(@old) IS NULL BEGIN RAISERROR ('The @old passed does not exist in the database', 16, 1) RETURN END IF USER_ID(@new) IS NULL BEGIN RAISERROR ('The @new passed does not exist in the database', 16, 1) RETURN END DECLARE owner_cursor CURSOR FOR SELECT name FROM sysobjects WHERE uid = USER_ID(@old) OPEN owner_cursor FETCH NEXT FROM owner_cursor INTO @objname WHILE (@@fetch_status <> -1) BEGIN SET @objname = @old + '.' + @objname EXEC sp_changeobjectowner @objname, @new FETCH NEXT FROM owner_cursor INTO @objname END CLOSE owner_cursor DEALLOCATE owner_cursor GO EXEC ChangeAll @old = 'mis', @new = 'dbo'转一个批量改表的所有者的过程
GO CREATE PROCEDURE ChangeAll (
@old sysname,
@new sysname
)
AS
DECLARE @objname sysname SET NOCOUNT ON IF USER_ID(@old) IS NULL
BEGIN
RAISERROR ('The @old passed does not exist in the database', 16, 1)
RETURN
END
IF USER_ID(@new) IS NULL
BEGIN
RAISERROR ('The @new passed does not exist in the database', 16, 1)
RETURN
END DECLARE owner_cursor CURSOR FOR SELECT name FROM sysobjects WHERE uid = USER_ID(@old)
OPEN owner_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
SET @objname = @old + '.' + @objname
EXEC sp_changeobjectowner @objname, @new
FETCH NEXT FROM owner_cursor INTO @objname
END CLOSE owner_cursor
DEALLOCATE owner_cursor
GO EXEC ChangeAll @old = 'mis', @new = 'dbo'转一个批量改表的所有者的过程