Modification des types de tables définies par l'utilisateur dans SQL Server

Question

Comment puis-je modifier un type de table défini par l'utilisateur dans SQL Server ?

Answer 1

Vous devez supprimer l'ancien type de table et en créer un nouveau. Toutefois, s'il existe des dépendances (procédures stockées qui l'utilisent), vous ne pourrez pas le supprimer. J'ai a posté une autre réponse sur la manière d'automatiser le processus d'abandon temporaire de toutes les procédures stockées, de modification de la table et de restauration des procédures stockées.

Answer 2

Vous ne pouvez pas ALTER/MODIFIER votre TYPE. Vous devez supprimer la colonne existante et la recréer avec le nom et le type de données corrects ou ajouter une nouvelle colonne.

Answer 3

J'ai dû faire ce type de tableau défini par l'utilisateur dans l'un de mes projets. Voici les étapes que j'ai suivies :

1. Trouver tous les SP en utilisant le type de tableau défini par l'utilisateur.
2. Enregistrer un script pour tous les PS trouvés.
3. Abandonner le(s) suppressif(s).
4. Enregistrez un script pour la table définie par l'utilisateur que vous souhaitez modifier. 4.5 Ajoutez la colonne supplémentaire ou les modifications dont vous avez besoin dans le type de tableau défini par l'utilisateur.
5. Supprimez le type de table défini par l'utilisateur.
6. Exécutez le script de création script pour le type de table défini par l'utilisateur.
7. Exécutez le script de création script pour le(s) PS.
8. Commencez alors à modifier le(s) PS en conséquence.

Answer 4

J'ai créé deux procédures stockées à cet effet. La première

create_or_alter_udt_preprocess prend le nom de l'udt en entrée, supprime toutes les procédures/fonctions stockées qui utilisent l'udt, supprime l'udt et renvoie un sql script pour recréer toutes les procédures/fonctions.

Le deuxième create_or_alter_udt_postprocess prend le script produit par la première proc et l'exécute.

Avec les deux procs, changer un udt peut être fait par :

1. appel create_or_alter_udt_preprocess ;
2. créer l'udt avec une nouvelle définition ;
3. appel create_or_alter_udt_postprocess ;

Utilisez une transaction pour éviter de perdre les procs d'origine en cas d'erreur.

create or ALTER   proc create_or_alter_udt_postprocess(@udt_postprocess_data xml)
as 
begin
    if @udt_postprocess_data is null 
        return;

    declare @obj_cursor cursor 
    set @obj_cursor = cursor fast_forward for 
    select n.c.value('.', 'nvarchar(max)') as definition
    from @udt_postprocess_data.nodes('/Objects/definition') as n(c)

    open @obj_cursor;

    declare @definition nvarchar(max);
    fetch next from @obj_cursor into @definition;
    while (@@fetch_status = 0)
    begin
        exec sp_executesql @stmt= @definition
        fetch next from @obj_cursor into @definition
    end

    CLOSE @obj_cursor;
    DEALLOCATE @obj_cursor; 
end

Create or ALTER   proc create_or_alter_udt_preprocess(@udt nvarchar(200), @udt_postprocess_data xml out) 
AS
    BEGIN
        set @udt_postprocess_data = null;
        if TYPE_ID(@udt) is null
            return;

        declare @drop_scripts nvarchar(max);
        SELECT @drop_scripts =  (
        (select N';'+ drop_script
            from 
        (
        SELECT 
            drop_script = N'drop ' + case sys.objects.type when 'P' then N'proc ' else N'function' end
                + sys.objects.name + N';' + + nchar(10) + nchar(13)
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects ON sys.objects.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@udt)
        ) dependencies
        FOR XML PATH (''), type
        ).value('.', 'nvarchar(max)')
         ) ;

        declare @postprocess_data xml;

        set @udt_postprocess_data =
        (SELECT 
            definition
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects ON sys.objects.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@udt)
        FOR XML PATH (''), root('Objects'));

        exec sp_executesql @stmt= @drop_scripts;
        exec sp_droptype @udt;
  END

Exemple d'utilisation :

begin tran
declare @udt_postprocess_data xml;

exec create_or_alter_udt_preprocess @udt= 'test_list', @udt_postprocess_data = @udt_postprocess_data out;
CREATE TYPE test_list AS TABLE(
    test_name nvarchar(50) NULL
);

exec create_or_alter_udt_postprocess @udt_postprocess_data = @udt_postprocess_data;

commit;

Code pour mettre en place l'exemple d'utilisation :

CREATE TABLE [dbo].[test_table](
    [test_id] [int] IDENTITY(1,1) NOT NULL, [test_name] [varchar](20) NULL
) ON [USERDATA]
GO

CREATE TYPE test_list AS TABLE(test_name nvarchar(20) NULL)
GO

create proc add_tests(
@test_list test_list readonly)
as 
begin
    SET NOCOUNT ON;
    insert into test_table(test_name)
    select test_name
    from @test_list;
end;

create proc add_tests2(
@test_list test_list readonly)
as 
begin
    SET NOCOUNT ON;
    insert into test_table(test_name)
    select test_name
    from @test_list;
end;