ALTER SCHEMA trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng câu lệnh ALTER SCHEMA trong SQL Server để chuyển các đối tượng từ một lược đồ này sang một lược đồ khác.

Tổng quan về câu lệnh ALTER SCHEMA trong SQL Server

Câu lệnh ALTER SCHEMA cho phép bạn chuyển các securable từ một lược đồ sang một lược đồ khác trong cùng một cơ sở dữ liệu.

Lưu ý rằng: securable là tài nguyên mà hệ thống ủy quyền của Database Engine kiểm soát quyền truy cập. Ví dụ, một bảng là một securable.

Sau đây là cú pháp của câu lệnh ALTER SCHEMA:

ALTER SCHEMA target_schema_name   
    TRANSFER [ entity_type :: ] securable_name;

Trong cú pháp này:

• target_schema_name là tên của một lược đồ trong cơ sở dữ liệu hiện tại mà bạn muốn di chuyển đối tượng vào đó. Lưu ý rằng nó không thể là SYS hoặc INFORMATION_SCHEMA.
• entity_type có thể là Đối tượng, Loại hoặc Tập hợp lược đồ XML. Nó mặc định là Đối tượng. entity_type đại diện cho lớp của thực thể mà chủ sở hữu đang được thay đổi.
• securable_name là tên của securable mà bạn muốn chuyển vào target_schema_name.

Nếu bạn di chuyển một stored procedure, function, view hoặc trigger, SQL Server sẽ không thay đổi tên lược đồ của các securable này. Do đó, bạn nên xóa và tạo lại các đối tượng này trong lược đồ mới thay vì sử dụng câu lệnh ALTER SCHEMA để di chuyển.

Nếu bạn di chuyển một đối tượng, ví dụ: table hoặc synonym, SQL Server sẽ không tự động cập nhật các tham chiếu cho các đối tượng này. Bạn phải sửa đổi thủ công các tham chiếu để phản ánh tên lược đồ mới. Ví dụ: nếu bạn di chuyển một table được tham chiếu trong một stored procedure, bạn phải sửa đổi stored procedure để phản ánh tên lược đồ mới.

Ví dụ về câu lệnh ALTER SCHEMA trong SQL Server

Đầu tiên, hãy tạo một bảng mới có tên offices trong lược đồ dbo:

CREATE TABLE dbo.offices
(
    office_id      INT
    PRIMARY KEY IDENTITY, 
    office_name    NVARCHAR(40) NOT NULL, 
    office_address NVARCHAR(255) NOT NULL, 
    phone          VARCHAR(20),
);

Tiếp theo, insert một số hàng vào bảng dbo.offices:

INSERT INTO dbo.offices(office_name, office_address)
VALUES ('Silicon Valley','400 North 1st Street, San Jose, CA 95130'),
    ('Sacramento','1070 River Dr., Sacramento, CA 95820');

Sau đó, tạo một stored procedure để tìm văn phòng theo id văn phòng:

CREATE PROC usp_get_office_by_id(
    @id INT
) AS
BEGIN
    SELECT 
        * 
    FROM 
        dbo.offices
    WHERE 
        office_id = @id;
END;

Cuối cùng, chuyển bảng dbo.offices này sang lược đồ sales:

ALTER SCHEMA sales TRANSFER OBJECT::dbo.offices;  

Nếu bạn thực thi stored procedure usp_get_office_by_id, SQL Server sẽ xuất hiện lỗi:

Msg 208, Level 16, State 1, Procedure usp_get_office_by_id, Line 5 [Batch Start Line 30]
Invalid object name 'dbo.offices'.

Để khắc phục lỗi này, hãy sửa đổi stored procedure sử dụng lược đồ mới:

ALTER PROC usp_get_office_by_id(
    @id INT
) AS
BEGIN
    SELECT 
        * 
    FROM 
        sales.offices
    WHERE 
        office_id = @id;
END;

Trong hướng dẫn này, bạn đã học cách sử dụng câu lệnh ALTER SCHEMA trong SQL Server để chuyển các securable từ lược đồ này sang lược đồ khác trong cùng một cơ sở dữ liệu.