Modification des types de table définis par l'utilisateur dans SQL Server

à ma connaissance, il est impossible de modifier un type de table.Vous peut créer le type avec un nom différent, puis déposer l'ancien type et le modifier pour le nouveau nom

Crédits jkrajes

comme dans msdn , c'est comme "la définition de type de table définie par l'utilisateur ne peut pas être modifiée après sa création".

c'est un peu un piratage, mais ça semble marcher. Vous trouverez ci-dessous les étapes et un exemple de modification d'un type de tableau. Une note est que sp_refreshsqlmodule échouera si le changement que vous avez fait au type table est un changement de rupture à cet objet, typiquement une procédure.

1. utiliser sp_rename pour renommer le type de table, je typiquement juste ajouter z à le début du nom.
2. Créer un nouveau type de table avec le nom d'origine et tout modification vous devez apporter à la table type.
3. Étape à travers chaque dépendance et exécuter sp_refreshsqlmodule .
4. supprimer le type de table renommé.

EXEC sys.sp_rename 'dbo.MyTableType', 'zMyTableType';
GO
CREATE TYPE dbo.MyTableType AS TABLE(
    Id INT NOT NULL,
    Name VARCHAR(255) NOT NULL
);
GO
DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.MyTableType', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;
GO
DROP TYPE dbo.zMyTableType;
GO

avertissement:

cela peut être destructeur pour votre base de données, donc vous voudrez tester cela sur un environnement de développement d'abord.

Voici des étapes simples qui minimisent l'ennui et ne nécessitent pas des scripts semi-automatiques sujets aux erreurs ou des outils coûteux.

gardez à l'esprit que vous pouvez générer des énoncés DROP / CREATE pour plusieurs objets à partir de la fenêtre de détails de L'Explorateur D'objets (lorsqu'ils sont générés de cette façon, les scripts DROP et CREATE sont groupés, ce qui rend facile d'insérer la logique entre Drop et Create actions):

1. Sauvegardez votre base de données au cas où quelque chose tourne mal!
2. génère automatiquement les déclarations DROP/CREATE pour toutes les dépendances (ou génère pour tous les objets" programmabilité " pour éliminer la difficulté de trouver des dépendances).
3. entre le DROP et les instructions CREATE [dependencies] (après tout DROP, avant tout CREATE), insérez les instructions DROP/CREATE [table type] générées, en faisant les modifications dont vous avez besoin avec CREATE TYPE.
4. Exécuter l' script, qui supprime toutes les dépendances/UDTTs et recrée ensuite [UDTTs avec des modifications] / dépendances.

si vous avez des projets plus petits où il pourrait être logique de changer l'architecture de l'infrastructure, envisager l'élimination des types de tableaux définis par l'utilisateur. Le cadre Entity et les outils similaires vous permettent de déplacer la plupart, sinon la totalité, de votre logique de données vers votre base de code où il est plus facile de la maintenir.

si vous pouvez utiliser un projet de base de données dans Visual Studio, vous pouvez faire vos modifications dans le projet et utiliser schema compare pour synchroniser les modifications à votre base de données.

de cette façon, laisser tomber et recréer les objets dépendants est géré par le script de changement.

vous ne pouvez pas modifier votre TYPE. Vous devez laisser tomber l'existant et le recréer avec le nom correct / type de données ou ajouter une nouvelle colonne/s

vous devriez laisser tomber l'ancien type de table et en créer un nouveau. Cependant, s'il a des dépendances (n'importe quelles procédures stockées l'utilisant), vous ne pourrez pas le laisser tomber. J'ai posté une autre réponse sur la façon d'automatiser le processus de suppression temporaire de toutes les procédures stockées, modifier la table de table et puis restaurer les procédures stockées.

Simon Zeinstra a trouvé la solution!

mais, J'ai utilisé Visual Studio community 2015 et je n'ai même pas eu à utiliser schema Comparer.

en utilisant SQL Server Object Explorer, j'ai trouvé mon type de table défini par l'utilisateur dans la base de données. J'ai cliqué droit-souris sur le type de table et sélectionné . Ceci a ouvert un onglet code dans L'IDE avec le code TSQL visible et modifiable . J'ai simplement changé la définition (dans mon cas, juste augmenté le taille d'un champ nvarchar) et cliqué sur le bouton Update Database en haut à gauche de l'onglet.

Hop! - un contrôle rapide dans le SSMS et la définition udtt a été modifiée.

brillant-Merci Simon.