-- 테이블 한개씩
alter schema [변경할 스키마명] transfer [기존 스키마명].[변경할 테이블명]

-- 전체 테이블 변경
SP_MSFOREACHTABLE 'sp_changeobjectowner "?","변경할 스키마"'


-- 사용법
-- "use 디비명"으로 해당 데이터베이스로 이동 후
-- 새로운 프로시저를 생성한다. 아래 소스 복사해서 그대로 사용
-- 쿼리 분석기를 열어서 [EXEC ChangeAllObjOwner @oldowner = '기존소유자', @newowner = '변경할소유자(ID)'] 이렇게 실행
-- 예) EXEC ChangeAllObjOwner @oldowner = 'dbo', @newowner = 'test'
-- 소유자 확인

IF OBJECT_ID('ChangeAllObjOwner') IS NOT NULL DROP PROC ChangeAllObjOwner
GO

CREATE PROCEDURE ChangeAllObjOwner (
  @oldowner sysname,
  @newowner sysname
)
AS
DECLARE @objname sysname
SET NOCOUNT ON

--check that the @oldowner exists in the database
IF USER_ID(@oldowner) IS NULL
  BEGIN
    RAISERROR ('The @oldowner passed does not exist in the database', 16, 1)
    RETURN
  END
--check that the @newowner exists in the database
IF USER_ID(@newowner) IS NULL
  BEGIN
    RAISERROR ('The @newowner passed does not exist in the database', 16, 1)
    RETURN
  END

DECLARE owner_cursor CURSOR FOR
  SELECT name FROM sysobjects WHERE uid = USER_ID(@oldowner)

OPEN owner_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
  SET @objname = @oldowner + '.' + @objname
  EXEC sp_changeobjectowner @objname, @newowner
  FETCH NEXT FROM owner_cursor INTO @objname
END

CLOSE owner_cursor
DEALLOCATE owner_cursor
GO
2013/04/22 15:45 2013/04/22 15:45

트랙백 주소 :: 이 글에는 트랙백을 보낼 수 없습니다