Modifier type défini par l'utilisateur dans Sql Server

C'est ce que j'ai l'habitude d'utiliser, mais un peu manuel:

/* Add a 'temporary' UDDT with the new definition */ 
exec sp_addtype t_myudt_tmp, 'numeric(18,5)', NULL 


/* Build a command to alter all the existing columns - cut and 
** paste the output, then run it */ 
select 'alter table dbo.' + TABLE_NAME + 
       ' alter column ' + COLUMN_NAME + ' t_myudt_tmp' 
from INFORMATION_SCHEMA.COLUMNS 
where DOMAIN_NAME = 't_myudt' 

/* Remove the old UDDT */ 
exec sp_droptype t_mydut


/* Rename the 'temporary' UDDT to the correct name */ 
exec sp_rename 't_myudt_tmp', 't_myudt', 'USERDATATYPE' 

il y a un bon exemple d'une étude plus complète de script ici

Il est intéressant de noter que ce script va comprendre des points de vue si vous en avez. J'ai couru et au lieu de exec avec inline généré un script que la sortie que j'ai ensuite modifié et a couru.

Aussi, si vous avez des fonctions/sprocs à l'aide de l'utilisateur defeined types vous aurez besoin de laisser avant l'exécution de votre script.

Leçon Apprise: à l'avenir, ne vous embêtez pas avec Udt ils sont plus de tracas que ce qu'ils valent.

SET NOCOUNT ON
DECLARE @udt VARCHAR(150)
DECLARE @udtschema VARCHAR(150)
DECLARE @newudtschema VARCHAR(150)
DECLARE @newudtDataType VARCHAR(150)
DECLARE @newudtDataSize smallint
DECLARE @OtherParameter VARCHAR(50)
SET @udt = 'Name' -- Existing UDDT
SET @udtschema = 'dbo' -- Schema of the UDDT
SET @newudtDataType = 'varchar' -- Data type for te new UDDT
SET @newudtDataSize = 500 -- Lenght of the new UDDT
SET @newudtschema = 'dbo' -- Schema of the new UDDT
SET @OtherParameter = ' NULL' -- Other parameters like NULL , NOT NULL
DECLARE @Datatype VARCHAR(50),
@Datasize SMALLINT
DECLARE @varcharDataType VARCHAR(50)
DECLARE @Schemaname VARCHAR(50),
@TableName VARCHAR(50),
@FiledName VARCHAR(50)
CREATE TABLE #udtflds
(
Schemaname VARCHAR(50),
TableName VARCHAR(50),
FiledName VARCHAR(50)
)
SELECT TOP 1
@Datatype = Data_type,
@Datasize = character_maximum_length
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   Domain_name = @udt
AND Domain_schema = @udtschema
SET @varcharDataType = @Datatype
IF @DataType Like '%char%'
AND @Datasize IS NOT NULL
AND ( @newudtDataType <> 'varchar(max)'
OR @newudtDataType <> 'nvarchar(max)'
)
BEGIN
SET @varcharDataType = @varcharDataType + '('
+ CAST(@Datasize AS VARCHAR(50)) + ')'
END
INSERT  INTO #udtflds
SELECT  TABLE_SCHEMA,
TABLE_NAME,
Column_Name
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   Domain_name = @udt
AND Domain_schema = @udtschema
DECLARE @exec VARCHAR(500)
DECLARE alter_cursor CURSOR
FOR SELECT  Schemaname,
TableName,
FiledName
FROM    #udtflds
OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
+ '  ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType
EXECUTE ( @exec
)
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
END
CLOSE alter_cursor
SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'
EXEC ( @exec
)
SET @varcharDataType = @newudtDataType
IF @newudtDataType Like '%char%'
AND @newudtDataSize IS NOT NULL
AND ( @newudtDataType <> 'varchar(max)'
OR @newudtDataType <> 'nvarchar(max)'
)
BEGIN
SET @varcharDataType = @varcharDataType + '('
+ CAST(@newudtDataSize AS VARCHAR(50)) + ')'
END
SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '
+ @varcharDataType + ' ' + @OtherParameter
EXEC ( @exec
)
OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
+ '  ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema
+ '].[' + @udt + ']'
EXECUTE ( @exec
)
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
END
CLOSE alter_cursor
DEALLOCATE alter_cursor
SELECT  *
FROM    #udtflds
DROP TABLE #udtflds

Un: http://www.sql-server-performance.com/2008/how-to-alter-a-uddt/ a remplacé http://www.sql-server-performance.com/faq/How_to_alter_a%20_UDDT_p1.aspx

Les solutions proposées ici ne peut être appliquée que si les types définis par l'utilisateur sont utilisés dans le tableau des définitions, et si l'UDT colonnes ne sont pas indexés.

Certains développeurs ont également des SP et des fonctions à l'aide de l'UDT paramètres, ce qui n'est pas couvert. (voir les commentaires sur le Robin lien et dans le Connecter l'entrée)

La connexion d'entrée à partir de 2007, a finalement été fermée au bout de 3 ans:

Merci de nous envoyer cette
suggestion, mais compte tenu de sa priorité
par rapport à de nombreux autres éléments dans
notre file d'attente, il est peu probable que nous
en fait le compléter. En tant que tel, nous sommes
la clôture de cette suggestion n'est pas de la
fix”.

J'ai essayé de résoudre un problème similaire Modifier les COLLECTIONS de schémas XML, et les mesures semblent s'applique principalement pour MODIFIER le TYPE, trop:

Pour supprimer un type défini par l'utilisateur, les étapes suivantes sont nécessaires:

• Si une colonne de la table de références de l'UDT, il doit être converti dans le type sous-jacent
• Si la colonne de la table a une contrainte par défaut, supprimer la contrainte par défaut
• Si une procédure ou une fonction a UDT paramètres, la procédure ou la fonction doit être abandonné
• Si il existe un index sur une colonne UDT, l'index doit être abandonné
• Si l'index est une clé primaire, toutes les clés doivent être tombé
• Si il y a des colonnes calculées basé sur un type de colonne, les colonnes calculées doivent être tombé
• Si il y a des indices sur ces colonnes calculées, les index doivent être supprimées
• Si il y a un schéma de vues, de fonctions ou de procédures fondées sur les tables contenant des colonnes UDT, ces objets doivent être tombé

La façon la plus simple de le faire est par le biais de Visual Studio, de l'explorateur d'objets, qui est également pris en charge dans l'édition de la Communauté.

Une fois que vous avez faites un lien à SQL server, accédez au type, cliquez droit et sélectionnez Afficher le Code, effectuez vos modifications sur le schéma du type défini par l'utilisateur et cliquez sur mettre à jour. Visual Studio devrait vous montrer toutes les dépendances de cet objet et de générer des scripts de mise à jour du type et de recompiler les dépendances.

J'ai rencontré ce problème avec des types personnalisés dans stockées procedres, et résolu avec le script ci-dessous. Je n'ai pas bien compris les scripts ci-dessus, et j'ai suivi la règle du "si vous ne savez pas ce qu'il fait, ne le faites pas".

En un mot, j'ai renommer l'ancien type, et en créer un nouveau avec le type d'original nom. Alors, je dis à SQL Server pour actualiser ses détails à propos de chaque procédure stockée en utilisant le type personnalisé. Vous disposez pour ce faire, que tout est encore "compilé" en référence à l'ancien type, même avec le changement de nom. Dans ce cas, le type j'avais besoin de changement a été "PrizeType". J'espère que cette aide. Je suis à la recherche de commentaires, de trop, j'ai donc apprendre 🙂

Notez que vous devrez peut-être aller à la Programmabilité > les Types > [Appropriées Type d'Utilisateur] et supprimer l'objet. J'ai trouvé que la BAISSE de TYPE ne semble pas toujours supprimer le type même après l'aide de l'instruction.

/* Rename the UDDT you want to replace to another name */ 
exec sp_rename 'PrizeType', 'PrizeTypeOld', 'USERDATATYPE';
/* Add the updated UDDT with the new definition */ 
CREATE TYPE [dbo].[PrizeType] AS TABLE(
[Type] [nvarchar](50) NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[ImageUrl] [varchar](max) NULL
);
/* We need to force sprocs to refresh with the new type... let's take care of that. */
/* Get a cursor over a list of all the sprocs that may use this and refresh them */
declare sprocs cursor
local static read_only forward_only
for
select specific_name from information_schema.routines where routine_type = 'PROCEDURE'
declare @sprocName varchar(max)
open sprocs
fetch next from sprocs into @sprocName
while @@fetch_status = 0
begin
print 'Updating ' + @sprocName;
exec sp_refreshsqlmodule @sprocName
fetch next from sprocs into @sprocName
end
close sprocs
deallocate sprocs
/* Drop the old type, now that everything's been re-assigned; must do this last */
drop type PrizeTypeOld;