ALTER SCHEMA (Transact-SQL)

Article
05/23/2023

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Transfère un élément sécurisable d'un schéma à un autre.

Conventions de la syntaxe Transact-SQL

Syntaxe

-- 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   
[;]

Arguments

schema_name
Nom d’un schéma de la base de données active où l’élément sécurisable doit être déplacé. Ne peut pas être SYS ou INFORMATION_SCHEMA.

<entity_type>
Classe de l'entité pour laquelle il y a un changement de propriétaire. Object est la valeur par défaut.

securable_name
Nom en une ou deux parties d’un élément sécurisable délimité aux schémas à transférer dans le schéma.

Notes

Les utilisateurs et les schémas sont complètement distincts.

L'instruction ALTER SCHEMA ne peut être utilisée que pour transférer des éléments sécurisables entre des schémas de la même base de données. Pour modifier ou supprimer un élément sécurisable au sein du même schéma, utilisez l'instruction ALTER ou DROP propre à cet élément sécurisable.

Si un nom en une seule partie est utilisé pour securable_name, les règles de résolution de noms en vigueur s’appliquent pour identifier l’emplacement de l’élément sécurisable.

Toutes les autorisations associées à cet élément sécurisable sont supprimées après le transfert de l'élément sécurisable vers le nouveau schéma. Si le propriétaire de l'élément sécurisable a été défini explicitement, il reste inchangé. Si la valeur SCHEMA OWNER a été attribuée au propriétaire de l'élément sécurisable, celui-ci reste SCHEMA OWNER ; une fois le transfert effectué, la valeur de SCHEMA OWNER est le propriétaire du nouveau schéma. L'identification principal_id du nouveau propriétaire sera NULL.

Le fait de déplacer une procédure stockée, une fonction, une vue ou un déclencheur ne change pas le nom de schéma, le cas échéant, de l’objet correspondant présent dans la colonne de définition de la vue de cataloguesys.sql_modules ou obtenu à l’aide de la fonction intégrée OBJECT_DEFINITION. Par conséquent, il est déconseillé d’utiliser ALTER SCHEMA pour renommer ces types d’objets. À la place, supprimez puis recréez l’objet dans son nouveau schéma.

Le fait de déplacer un objet tel qu’une table ou un synonyme ne met pas automatiquement à jour les références à cet objet. Vous devez modifier manuellement tout objet qui référence l’objet transféré. Par exemple, si vous déplacez une table et que cette table est référencée dans un déclencheur, vous devez modifier le déclencheur pour refléter le nom du nouveau schéma. Utilisez sys.sql_expression_dependencies pour répertorier les dépendances de l’objet avant de le déplacer.

Pour changer le schéma d’une table à l’aide de SQL Server Management Studio, dans l’Explorateur d’objets, cliquez avec le bouton droit sur la table, puis sélectionnez Conception. Appuyez sur F4 pour ouvrir la fenêtre Propriétés. Dans la zone Schéma, sélectionnez un nouveau schéma.

ALTER SCHEMA utilise un verrou de niveau de schéma.

Attention

Avec l'arrivée de SQL Server 2005, le comportement des schémas a changé. Ainsi, le code qui suppose que les schémas sont équivalents aux utilisateurs de base de données peut ne plus renvoyer des résultats corrects. Vous ne devez pas recourir aux anciens affichages catalogue, notamment sysobjects, dans une base de données où une des instructions DDL suivantes a été utilisée : 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. Dans ces bases de données, vous devez utiliser les nouveaux affichages catalogue. Les nouveaux affichages catalogue tiennent compte de la séparation des principaux et des schémas introduite dans SQL Server 2005 Pour plus d'informations sur les affichages catalogue, consultez Affichages catalogue (Transact-SQL).

Autorisations

Pour transférer un élément sécurisable provenant d'un autre schéma, l'utilisateur actuel doit bénéficier de l'autorisation CONTROL sur l'élément sécurisable (et non sur le schéma) et de l'autorisation ALTER sur le schéma cible.

Si l’élément sécurisable est soumis à une spécification EXECUTE AS OWNER et que le propriétaire est défini sur SCHEMA OWNER, l’utilisateur doit également bénéficier de l’autorisation IMPERSONATE sur le propriétaire du schéma cible.

Toutes les autorisations associées à l'élément sécurisable soumis à un transfert sont supprimées lors du déplacement de cet élément.

Exemples

R. Transfert de la propriété d'une table

L’exemple suivant modifie le schéma HumanResources en transférant la table Address du schéma Person vers le schéma HumanResources.

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

B. Transfert de propriété d'un type

L'exemple suivant crée un type dans le schéma Production, puis transfère le type vers le schéma Person.

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

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

C. Transfert de la propriété d'une table

L’exemple suivant crée une table Region dans le schéma dbo, crée un schéma Sales, puis déplace la table Region du schéma dbo vers le schéma Sales.

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

Voir aussi

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

Partager via