SQL Server创建、更改和删除架构

SQL Server创建架构

学习如何使用SQL Server CREATE SCHEMA在当前数据库中创建新架构。

SQL Server中的架构是什么

架构是包括表，视图，触发器，存储过程，索引等在内的数据库对象的集合。架构与称为架构所有者的用户名相关联，该用户名是逻辑上相关的数据库对象的所有者。

架构始终属于一个数据库。另一方面，数据库可以具有一个或多个架构。

SQL Server中的内置架构

SQL Server提供了我们与具有相同名称的内置数据库用户和角色，例如一些预先定义的架构：dbo，guest，sys，和INFORMATION_SCHEMA。

请注意，SQL Server 为系统对象保留sys和INFORMATION_SCHEMA架构，因此，您不能在这些架构中创建或删除任何对象。

新创建的数据库的默认架构为dbo，由dbo用户帐户拥有。默认情况下，当您使用CREATE USER命令创建新用户时，该用户将dbo作为其默认架构。

SQL Server创建架构语句概述

该创建架构语句允许您在当前数据库中创建新的架构。

CREATE SCHEMA schema_name
[AUTHORIZATION owner_name]

用这种语法，

• 首先，在CREATE SCHEMA子句中指定要创建的架构的名称。
• 其次，在AUTHORIZATION关键字之后指定架构的所有者。

SQL Server创建架构语句示例

以下示例显示如何使用该CREATE SCHEMA语句创建customer_services架构：

CREATE SCHEMA customer_services;
GO

请注意，该GO命令指示SQL Server Management Studio将SQL语句发送GO到要执行的服务器的语句。

执行该语句后，您可以在数据库名称的“ 安全性”>“架构 ” 下找到新创建的架构。

如果要列出当前数据库中的所有架构，则可以从中查询架构，sys.schemas如以下查询所示：

SELECT
    s.name AS schema_name,
    u.name AS schema_owner
FROM
    sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY
    s.name;

这是输出：

 

 拥有customer_services架构后，您可以为架构创建对象。例如，以下语句创建一个jobs在customer_services架构中命名的新表：

CREATE TABLE jobs(
    job_id INT PRIMARY KEY IDENTITY,
    customer_id INT NOT NULL,
    description VARCHAR(200),
    created_at DATETIME2 NOT NULL
);

SQL Server更改架构

如何使用SQL Server更改架构将安全对象从一种架构转移到另一种架构。

SQL Server更改架构概述

该ALTER SCHEMA语句允许您将安全对象从架构转移到同一数据库中的另一个架构。

请注意，安全对象是数据库引擎授权系统控制对其进行访问的资源。例如，表是安全的。

下面显示了该ALTER SCHEMA语句的语法：

ALTER SCHEMA target_schema_name  
    TRANSFER [ 实体对象 :: ] securable_name;

使用以下语法：

• target_schema_name是当前数据库中您要将对象移动到的架构的名称。 请注意，它不能是SYS或INFORMATION_SCHEMA。
• 实体类型可以是对象，类型或XML架构集合。 默认为对象。 entity_type代表要为其更改所有者的实体的类。
• object_name是要移到target_schema_name中的安全对象的名称

如果移动存储过程，函数，视图或触发器，则SQL Server不会更改这些安全对象的架构名称。 因此，建议您在新架构中删除并重新创建这些对象，而不要使用ALTER SCHEMA语句进行移动。

如果移动对象（例如表或同义词），SQL Server将不会自动更新这些对象的引用。您必须手动修改引用以反映新的架构名称。例如，如果移动存储过程中引用的表，则必须修改存储过程以反映新的架构名称。

SQL Server更改架构示例

首先，在dbo架构中创建一个名为office的新表：

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),
);

接下来，将一些行插入dob.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');

然后，创建一个存储过程，按办公室ID查找办公室：

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

之后，将此dbo.offices表转移到sales架构：

ALTER SCHEMA sales TRANSFER OBJECT::dbo.offices; 

如果执行usp_get_office_by_id存储过程，SQL Server将发出错误：

 

 最后，手动修改存储过程以反映新的架构：

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

SQL Server DROP架构

如何使用SQL Server DROP SCHEMA语句从数据库中删除架构。

SQL Server删除架构语句概述

该DROP SCHEMA语句允许您从数据库中删除架构。下面显示了该DROP SCHEMA语句的语法：

DROP SCHEMA [IF EXISTS] schema_name;

使用以下语法：

• 首先，指定要删除的架构的名称。如果架构包含任何对象，则该语句将失败。因此，您必须在删除架构之前删除架构中的所有对象。
• 其次，IF EXISTS仅当架构存在时，才使用该选项有条件地删除该架构。尝试删除不带该IF EXISTS选项的不存在的架构将导致错误。

SQL Server删除架构语句示例

首先，创建一个名为logistics的新架构：

CREATE SCHEMA logistics;
GO

接下来，在logistics架构内部创建一个名为delivery的新表：

CREATE TABLE logistics.deliveries
(
    order_id        INT
    PRIMARY KEY,
    delivery_date   DATE NOT NULL,
    delivery_status TINYINT NOT NULL
);

然后，删除架构logistics：

DROP SCHEMA logistics;

SQL Server发出以下错误，因为架构不为空。

 

之后，删除表格Logistics.deliveries：

DROP TABLE logistics.deliveries;

最后，DROP SCHEMA再次发出以删除logistics架构：

DROP SCHEMA IF EXISTS logistics;

现在，logistics架构已从数据库中删除。