本文将介绍数据库模式(SCHEMA)在SQL Server中的应用及其优点。

在模式中创建新表

要创建诸如表之类的对象，我们需要指定将在其中创建对象的模式名称。例如，以下脚本在不同的模式 [HR]、[Admin] 和 [Fin] 中创建 [TableA]。

CREATE TABLEHR.TableA

(

ID int identity(1,1) PRIMARY KEY,

[Name] varchar(100)

)

CREATE TABLE [Admin].TableA

(

ID int identity(1,1) PRIMARY KEY,

[Name] varchar(100)

)

CREATE TABLE [Fin].TableA

(

ID int identity(1,1) PRIMARY KEY,

[Name] varchar(100)

)

您可以 sys.tables 和 sys.schema 系统表以列出表名及其模式。

例如，以下查询返回[TableA] 及其架模式。

SELECT

s.name AS SchemaName,

t.name AS TableName

FROM sys.tables t

INNER JOIN sys.schemas s

ON t.schema_id = s.schema_id

WHERE t.name ='TableA'

实例

如果在创建对象时未指定模式，SQL Server 将使用默认模式。例如，以下脚本在 dbo 模式中创建了一个 TableA。

CREATE TABLE TableA

(

ID int identity(1,1) PRIMARY KEY,

[Name] varchar(100)

)

实例

注意：可以在数据库的不同模式中创建具有相似名称的表。

所以需要指定表的模式才能从正确的模式中获取数据。

例如，如上所示，我们在所有架构中都有[TableA]。因此，如果您直接选择记录而不指定架构，它会在默认 DBO 架构中查找对象。

因此，需要指定模式名称，例如 SELECT * FROM HR.TableA 以进行数据检索或执行任何操作。

在模式中创建存储过程

同样，您可以在指定模式中创建对象，例如存储过程。例如，以下脚本在 HR 模式中创建一个 SP。

CREATE PROCEDURE HR.GetEmpData

AS

BEGIN

SELECT * FROM [HR].[TableA]

END

将对象转移到不同的模式

在某些时候，可能需要将对象移动到特定模式。例如，假设创建了一个新模式[Org]，并且您希望您的 [HR].[TableA] 从 [HR] 模式移动到 [Org] 模式。

在这种情况下，可以使用具有以下语法的Alter SCHEMA 命令。

ALTER SCHEMA target_schema_name

TRANSFER [ entity_type :: ] securable_name;

脚本

CREATE SCHEMA [ORG]

GO

ALTER SCHEMA ORG TRANSFER HR.TableA

现在，将存储过程[HR].[GetEmpData] 转移到 [Org] 模式。

ALTER SCHEMA ORG TRANSFER HR.GetEmpData

执行脚本后，它会显示 [Org]模式中的存储过程。

但是，在SP里面，代码仍然引用[HR].[TableA] 模式。

实例

因此，不应使用 ALTER SCHEMA 來移动存储过程、函数或视图，因为它可能具有对象的引用。相反，可以删除、创建或更改SP，如下所示。

实例

删除模式

可以在 SQL Server 数据库中删除模式，但模式不应包含任何对象。

例如，如果尝试删除 [Org] 模式，它会给出一个错误，即无法删除模式，因为对象 GetEmpData正在引用它。

实例

因此，可以将对象转移到不同的模式或先删除对象。一旦我们删除或移动了数据库模式中的所有对象，您就可以删除模式。

注意：不能删除系统模式，例如 dbo、information_schema、sys。

使用数据库模式的优势

数据库模式为我们提供了在数据库中创建逻辑对象组的灵活性。如果多个团队使用同一个数据库，我们可以设计各种模式来分组对象。

数据库模式可帮助数据库专业人员管理访问，因为可以控制对用户各自模式的访问，而不是提供对整个数据库的访问。

可以更有效地管理数据库，因为它允许多个模式中的相同对象显示为不同的逻辑组。

可以在不同的模式中快速移动对象。

模式所有权可以分配给任何数据库主体或角色，所有权也可以转移。

它提供了额外的安全层，因为您需要知道正确的对象模式来查询或操作数据。还可以控制对模式和模式拥有的对象的访问。