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

A ma connaissance, il est impossible de modifier un type de table. Vous pouvez créer le type avec un nom différent, puis supprimer l'ancien type et le modifier avec le nouveau nom. et le modifier avec le nouveau nom

Crédits à jkrajes

Conformément à la msdn La définition du type de table défini par l'utilisateur ne peut pas être modifiée après sa création.

Answer 2

Il s'agit d'une sorte de piratage, mais qui semble fonctionner. Vous trouverez ci-dessous les étapes et un exemple de modification d'un type de table. Il faut noter que le module sp_refreshsqlmodule échouera si la modification que vous avez apportée au type de table est une rupture dans cet objet, typiquement une procédure.

1. Utilice sp_rename pour renommer le type de tableau, j'ajoute généralement z au début de t. au début du nom.
2. Créer un nouveau type de tableau avec le nom d'origine et les modifications éventuelles que vous devez apporter au type de table.
3. Passez en revue chaque dépendance et exécutez sp_refreshsqlmodule sur elle.
4. Supprimez 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 :

Cette opération peut s'avérer destructive pour votre base de données, c'est pourquoi vous devez d'abord la tester dans un environnement de développement.

Answer 3

Voici des étapes simples qui minimisent les tâches fastidieuses et ne nécessitent pas de scripts semi-automatiques sujets aux erreurs ou d'outils coûteux.

N'oubliez pas que vous pouvez générer des instructions DROP/CREATE pour plusieurs objets à partir de la fenêtre Détails de l'explorateur d'objets (lorsqu'elles sont générées de cette manière, les scripts DROP et CREATE scripts sont regroupés, ce qui facilite l'insertion de la logique entre les actions Drop et Create) :

1. Sauvegardez votre base de données en cas de problème !
2. Générer automatiquement les instructions DROP/CREATE pour toutes les dépendances (ou les générer pour tous les objets de "programmabilité" afin d'éliminer le fastidieux travail de recherche des dépendances).
3. Entre les instructions DROP et CREATE [dépendances] (après toutes les instructions DROP, avant toutes les instructions CREATE), insérez les instructions DROP/CREATE [type de table] générées, en apportant les modifications nécessaires avec CREATE TYPE.
4. Exécutez le script, qui supprime toutes les dépendances/UDTT et recrée ensuite les [UDTT avec modifications]/dépendances.

---

Si vous avez des projets plus modestes pour lesquels il peut être judicieux de modifier l'architecture de l'infrastructure, envisagez d'éliminer les types de tables définis par l'utilisateur. Entity Framework et d'autres outils similaires vous permettent de déplacer la plupart, voire la totalité, de votre logique de données dans votre base de code, où elle est plus facile à maintenir.

Pour générer les instructions DROP/CREATE pour plusieurs objets, vous pouvez cliquer avec le bouton droit de la souris sur votre base de données > Tâches > Générer des scripts... (comme indiqué dans la capture d'écran ci-dessous). Remarque :

1. DROP Les déclarations sont antes de CREATE déclarations
2. DROP sont en ordre de dépendance (c'est-à-dire l'inverse de CREATE)
3. CREATE sont en ordre de dépendance

Answer 4

Simon Zeinstra a trouvé la solution !

Mais j'ai utilisé Visual Studio community 2015 et je n'ai même pas eu besoin d'utiliser la comparaison de schémas.

En utilisant l'Explorateur d'objets SQL Server, j'ai trouvé mon type de table défini par l'utilisateur dans la base de données. J'ai fait un clic droit sur le type de table et j'ai sélectionné . Cela a ouvert un onglet de code dans l'IDE avec le code TSQL visible et le code de la table. modifiable . J'ai simplement modifié la définition (dans mon cas, j'ai simplement augmenté la taille d'un champ nvarchar) et j'ai cliqué sur le bouton Mise à jour de la base de données en haut à gauche de l'onglet.

Hé Presto ! - une vérification rapide dans SSMS et la définition udtt a été modifiée.

Brillant - merci Simon.

Answer 5

Si vous pouvez utiliser un projet de base de données dans Visual Studio, vous pouvez apporter vos modifications dans le projet et utiliser la comparaison de schémas pour synchroniser les modifications avec votre base de données.

De cette manière, l'abandon et la recréation des objets dépendants sont gérés par le script de modification.