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

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

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

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 [LC029999];
GO

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

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

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

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

/* 执行存储过程的demo
EXEC dbo.upx_changeSchema 
        'dbo',        -- 旧schema名称
        'lc029999';    -- 新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;

 

上一篇:SQL SERVER查看表字段信息


下一篇:GFS Client