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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了sqlserver批量修改数据库对象的schema架构，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2042字，纯文字阅读大概需要3分钟。

内容图文

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;

 

内容总结

以上是互联网集市为您收集整理的sqlserver批量修改数据库对象的schema架构全部内容，希望文章能够帮你解决sqlserver批量修改数据库对象的schema架构所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---