sqlserver批量修改数据库对象的schema架构

SQL Server数据库有登录名、用户、架构、角色等概念，在此不重复解释。

其中架构名，原则上是不允许修改的（重命名），但当特殊业务场景需要时，可以通过创建新架构名，然后批量修改对象的架构来实现。

-- ALTER SCHEMA 新架构 TRANSFER 旧架构.对象名称

ALTER SCHEMA [LC019999] TRANSFER dbo.[TestTkk]

 

需要进行修改默认schema的数据库对象有：表、视图、函数、存储过程，其他附属对象跟随父对象走，如主外键、约束、索引、默认值等

SELECT SCHEMA_NAME([SCHEMA_ID]) AS shemaName, *
FROM sys.objects
WHERE TYPE IN ('U', 'V', 'FN', 'P')
--AND SCHEMA_NAME([SCHEMA_ID]) = @old_schema
AND [OBJECT_ID] != OBJECT_ID('dbo.upx_changeSchema')

 

具体步骤如下：

1、先创建一个新的架构（登录名、用户、架构）；2、将原架构下的数据库对象批量改到新架构下；3、删除原有架构

-- 创建服务器的login[登录名]，设置密码、检查策略，同时设置默认数据库
CREATE LOGIN LC039999 
    WITH PASSWORD='aaaaaa', 
    CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF, 
    DEFAULT_DATABASE = [Northwind];
GO

-- 给指定login[登录名]设置服务器角色
ALTER SERVER ROLE [dbcreator] ADD MEMBER [LC039999];
GO


USE [Northwind];

-- 在特定数据库下创建用户，绑定[登录名]，同时设置默认[schema]
CREATE USER LC039999 FROM LOGIN LC039999 WITH DEFAULT_SCHEMA=LC039999;
GO

-- 给数据库用户设置数据库角色
ALTER ROLE [db_owner] ADD MEMBER [LC039999];
GO

-- 创建数据库[schema]，设置schema所有者为指定数据库用户
CREATE SCHEMA LC039999 AUTHORIZATION LC039999;
GO

在业务库执行如下SQL，可以创建批量修改schema的存储过程。

创建完成后，执行该存储过程即可批量修改。

建议：执行前做好数据库备份。

/* 执行存储过程的demo
EXEC dbo.upx_changeSchema 
        'dbo',        -- 旧schema名称
        'lc039999';    -- 新schema名称

*/

IF OBJECT_ID('dbo.upx_changeSchema') IS NOT NULL
BEGIN
    DROP PROC dbo.upx_changeSchema;
END;

GO
CREATE PROC dbo.upx_changeSchema(@old_schema VARCHAR(200), @new_schema VARCHAR(200))
AS
BEGIN
    --declare @old_schema varchar(200) = 'dbo';
    --declare @new_schema varchar(200) = 'dbo';
    DECLARE @objName VARCHAR(200);
    DECLARE csr CURSOR
       FOR SELECT NAME 
            FROM sys.objects 
            WHERE TYPE IN ('U', 'V', 'FN', 'P') 
                AND SCHEMA_NAME([SCHEMA_ID]) = @old_schema 
                AND [OBJECT_ID] != OBJECT_ID('dbo.upx_changeSchema')
 
    OPEN csr
      FETCH NEXT FROM csr INTO @objName
 
    WHILE (@@FETCH_STATUS=0)
    BEGIN
        --PRINT 'ALTER SCHEMA ' + @new_schema + ' TRANSFER ' + @old_schema + '.' + @objName
        exec('ALTER SCHEMA ' + @new_schema + ' TRANSFER ' + @old_schema + '.' + @objName);
        FETCH NEXT FROM csr INTO @objName
    END

    CLOSE csr
    DEALLOCATE csr
END;

 

最后，根据业务需要决定是否删除原有的架构、用户及登录名。

注意：有严格的顺序要求。

DROP SCHEMA LC029999;
DROP USER   LC029999;
DROP LOGIN  LC029999;