ALTER SCHEMA (Transact-SQL)

Artikel
05/23/2023

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Überträgt ein sicherungsfähiges Element zwischen Schemas.

Transact-SQL-Syntaxkonventionen

Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
ALTER SCHEMA schema_name   
   TRANSFER [ <entity_type> :: ] securable_name   
[;]  
  
<entity_type> ::=  
    {  
    Object | Type | XML Schema Collection  
    }

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse and Microsoft Fabric
  
ALTER SCHEMA schema_name   
   TRANSFER [ OBJECT :: ] securable_name   
[;]

Argumente

schema_name
Der Name eines Schemas in der aktuellen Datenbank, in das das sicherungsfähige Element verschoben wird. Kann weder SYS noch INFORMATION_SCHEMA sein.

<entity_type>
Die Klasse der Entität, für die der Besitzer geändert wird. Object ist der Standardwert.

securable_name
Bezeichnet den ein- oder zweiteiligen Namen eines sicherungsfähigen schemabezogenen Elements, das in das Schema verschoben werden soll.

Bemerkungen

Benutzer und Schemas vollkommen voneinander getrennt.

ALTER SCHEMA kann nur zum Verschieben von sicherungsfähigen Elementen zwischen Schemas in derselben Datenbank verwendet werden. Zum Ändern oder Löschen eines sicherungsfähigen Elements in einem Schema verwenden Sie die für das sicherungsfähige Element spezifische ALTER- oder DROP-Anweisung.

Wenn ein einteiliger Name für securable_name verwendet wird, werden die derzeit gültigen Regeln zur Namensauflösung zum Auffinden des sicherungsfähigen Elements angewendet.

Alle dem sicherungsfähigen Element zugeordneten Berechtigungen werden gelöscht, wenn das sicherungsfähige Element in das neue Schema verschoben wird. Wurde der Besitzer des sicherungsfähigen Elements explizit festgelegt, bleibt der Besitzer unverändert. Wenn der Besitzer des sicherungsfähigen Elements auf SCHEMA OWNER festgelegt wurde, bleibt diese Einstellung zunächst erhalten. Nach dem Verschieben wird SCHEMA OWNER jedoch zum Besitzer des neuen Schemas aufgelöst. principal_id des neuen Besitzers ist NULL.

Durch das Verschieben einer gespeicherten Prozedur, Funktion, Sicht oder eines Triggers wird der möglicherweise vorhandene Schemaname des entsprechenden Objekts nicht geändert. Dies gilt sowohl für eine Bezeichnung in der definition-Spalte der sys.sql_modules-Katalogsicht als auch für eine Bezeichnung, die über die integrierte Funktion OBJECT_DEFINITION abgerufen wird. Daher ist es empfehlenswert, ALTER SCHEMA nicht zum Verschieben dieser Objekttypen zu verwenden. Löschen Sie stattdessen das Objekt, und erstellen Sie es neu im zugehörigen neuen Schema.

Beim Verschieben eines Objekts wie z.B. einer Tabelle oder eines Synonyms werden Verweise auf das Objekt nicht automatisch aktualisiert. Sie müssen Objekte, die auf das verschobene Objekt verweisen, manuell ändern. Wenn Sie z.B. eine Tabelle verschieben und in einem Trigger auf diese Tabelle verwiesen wird, müssen Sie den Trigger ändern, damit für ihn der Schemaname verwendet wird. Mit sys.sql_expression_dependencies können Sie Objektabhängigkeiten auflisten, bevor Sie das Objekt verschieben.

Wenn Sie das Schema einer Tabelle mit SQL Server Management Studio ändern möchten, können Sie im Objekt-Explorer mit der rechten Maustaste auf die Tabelle und anschließend auf Entwurf klicken. Drücken Sie F4, um das Eigenschaftenfenster zu öffnen. Wählen Sie im Feld Schema ein neues Schema aus.

ALTER SCHEMA verwendet eine Sperre auf Schemaebene.

Achtung

Ab SQL Server 2005 hat sich das Verhalten der Schemas geändert. Daher gibt Code, der voraussetzt, dass Schemas mit Datenbankbenutzern identisch sind, nunmehr keine richtigen Ergebnisse mehr zurück. Alte Katalogsichten, einschließlich sysobjects, sollten nicht in einer Datenbank verwendet werden, in der jemals eine der folgenden DDL-Anweisungen verwendet wurde: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In derlei Datenbanken müssen Sie stattdessen die neuen Katalogansichten verwenden. In den neuen Katalogsichten wird die Trennung zwischen Prinzipalen und Schemas berücksichtigt, die in SQL Server 2005 eingeführt wurde. Weitere Informationen zu Katalogsichten finden Sie unter Katalogsichten (Transact-SQL).

Berechtigungen

Für die Übertragung eines sicherungsfähigen Elements aus einem anderen Schema benötigt der aktuelle Benutzer die CONTROL-Berechtigung für das sicherungsfähige Element (nicht das Schema) sowie die ALTER-Berechtigung für das Zielschema.

Wenn das sicherungsfähige Element über eine EXECUTE AS OWNER-Spezifikation verfügt und der Benutzer als SCHEMA OWNER festgelegt ist, benötigt der Benutzer auch die IMPERSONATE-Berechtigung für den Besitzer des Zielschemas.

Alle dem sicherungsfähigen Element, das übertragen wird, zugeordneten Berechtigungen werden gelöscht, wenn es verschoben wird.

Beispiele

A. Übertragen des Besitzes einer Tabelle

Im folgenden Beispiel wird das HumanResources-Schema durch Übertragen der Address-Tabelle aus dem Person-Schema in das HumanResources-Schema geändert.

USE AdventureWorks2022;  
GO  
ALTER SCHEMA HumanResources TRANSFER Person.Address;  
GO

B. Übertragen des Besitzes eines Typs

Im folgenden Beispiel wird ein Typ im Production-Schema erstellt und dann an das Person-Schema übertragen.

USE AdventureWorks2022;  
GO  
  
CREATE TYPE Production.TestType FROM [VARCHAR](10) NOT NULL ;  
GO  
  
-- Check the type owner.  
SELECT sys.types.name, sys.types.schema_id, sys.schemas.name  
    FROM sys.types JOIN sys.schemas   
        ON sys.types.schema_id = sys.schemas.schema_id   
    WHERE sys.types.name = 'TestType' ;  
GO  
  
-- Change the type to the Person schema.  
ALTER SCHEMA Person TRANSFER type::Production.TestType ;  
GO  
  
-- Check the type owner.  
SELECT sys.types.name, sys.types.schema_id, sys.schemas.name  
    FROM sys.types JOIN sys.schemas   
        ON sys.types.schema_id = sys.schemas.schema_id   
    WHERE sys.types.name = 'TestType' ;  
GO

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

C. Übertragen des Besitzes einer Tabelle

Im folgenden Beispiel werden eine Region-Tabelle im dbo-Schema und ein Sales-Schema erstellt. Anschließend wird die Region-Tabelle aus dem dbo-Schema in das Sales-Schema verschoben.

CREATE TABLE dbo.Region   
    (Region_id INT NOT NULL,  
    Region_Name CHAR(5) NOT NULL)  
WITH (DISTRIBUTION = REPLICATE);  
GO  
  
CREATE SCHEMA Sales;  
GO  
  
ALTER SCHEMA Sales TRANSFER OBJECT::dbo.Region;  
GO

Weitere Informationen

CREATE SCHEMA (Transact-SQL)
DROP SCHEMA (Transact-SQL)
EVENTDATA (Transact-SQL)

Teilen über