SQL Server - script pour mettre à jour la base de données des colonnes de type varchar nvarchar si ce n'est déjà nvarchar

Je suis dans une situation où je dois mettre à jour une structure de base de données varchar nvarchar à l'aide d'un script. Depuis ce script est exécuté à chaque fois une configuration de l'application est exécutée, je serais plutôt de déterminer si une colonne a déjà été changé le type de données nvarchar et de ne pas effectuer un alter sur la table. Les bases de données qui je doit prendre en charge SQL Server 2000, 2005 et 2008.

InformationsquelleAutor Michelle | 2008-10-07

alter-table sql-server

La requête suivante devrait obtenir ce dont vous avez besoin:

IF EXISTS 
  (SELECT *
   FROM sysobjects syo
   JOIN syscolumns syc ON
     syc.id = syo.id
   JOIN systypes syt ON
     syt.xtype = syc.xtype
   WHERE 
     syt.name = 'nvarchar' AND
     syo.name = 'MY TABLE NAME' AND
     syc.name = 'MY COLUMN NAME')
BEGIN
   ALTER ...
END

InformationsquelleAutor Peter

26

Vous pouvez exécuter le script suivant qui vous donnera une série de MODIFIER les commandes:
```
SELECT 'ALTER TABLE ' + isnull(schema_name(syo.id), 'dbo') + '.' +  syo.name 
    + ' ALTER COLUMN ' + syc.name + ' NVARCHAR(' + case syc.length when -1 then 'MAX' 
        ELSE convert(nvarchar(10),syc.length) end + ');'
   FROM sysobjects syo
   JOIN syscolumns syc ON
     syc.id = syo.id
   JOIN systypes syt ON
     syt.xtype = syc.xtype
   WHERE 
     syt.name = 'varchar' 
    and syo.xtype='U'
```
Il y a, cependant, un couple de rapide mises en garde pour vous.
1. Cela ne fera que créer des tables. Vous aurez envie de numériser tous vos sprocs et des fonctions afin de s'assurer qu'ils sont modifiés pour NVARCHAR ainsi.
2. Si vous avez un VARCHAR > 4000, vous aurez besoin de le modifier pour être NVARCHAR(MAX)
Mais celles-ci doivent être facilement réalisable avec ce modèle.

Si vous le souhaitez exécuter automatiquement, vous pouvez le mettre dans un WHILE clause.
- Cela a fonctionné à merveille pour moi. Grand petit script!
- Soyez prudent avec cette raison, vous aurez toutes les valeurs par défaut pour une nouvelle colonne. E. g., une valeur non nulle de type varchar sera créé comme un nullable nvarchar.
InformationsquelleAutor Josef

Le problème avec Josef réponse est que cela ne changerait NOT NULL champs de NULL après l'exécution de requêtes. La suite de la manipulation fixe:

SELECT cmd = 'alter table [' + c.table_schema + '].[' + c.table_name 
 + '] alter column [' + c.column_name + '] nvarchar('
 +CASE WHEN CHARACTER_MAXIMUM_LENGTH<=4000
       THEN CAST(CHARACTER_MAXIMUM_LENGTH as varchar(10)) ELSE 'max' END+')' 
 + CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END,*
FROM information_schema.columns c
WHERE c.data_type='varchar' 
ORDER BY CHARACTER_MAXIMUM_LENGTH desc

Crédits à Igor répondre

InformationsquelleAutor Nezam

Fixe la question de l'espace et a ajouté schéma

SELECT 'ALTER TABLE [' + isnull(schema_name(syo.object_id), sysc.name) + '].[' +  syo.name 
    + '] ALTER COLUMN ' + syc.name + ' NVARCHAR(' + case syc.max_length when -1 then 'MAX' 
        ELSE convert(nvarchar(10),syc.max_length) end + ');'
   FROM sys.objects syo
   JOIN sys.columns syc ON
     syc.object_id= syo.object_id
   JOIN sys.types syt ON
     syt.system_type_id = syc.system_type_id
    JOIN sys.schemas sysc ON
    syo.schema_id=sysc.schema_id
   WHERE 
     syt.name = 'varchar' 
    and syo.type='U'

InformationsquelleAutor Appyks

Plus mis à jour pour corriger MAX remplacé par -1.

SELECT cmd = 'ALTER TABLE [' + c.table_schema + '].[' + c.table_name 
 + '] ALTER COLUMN [' + c.column_name + '] NVARCHAR('
 +CASE WHEN CHARACTER_MAXIMUM_LENGTH<=4000 THEN 
 CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN
 'MAX' ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) END ELSE 'MAX' END+')' 
 + CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END,*
FROM information_schema.columns c
WHERE c.data_type='VARCHAR' 
ORDER BY CHARACTER_MAXIMUM_LENGTH DESC

De crédit à Nezam Réponse

Et un autre pour gérer les valeurs par défaut:

SELECT cmd = 
CASE WHEN name IS NOT NULL THEN
'ALTER TABLE ' + c.table_name + ' DROP CONSTRAINT ' + d.name + '; ' +
'ALTER TABLE [' + c.table_schema + '].[' + c.table_name + '] ALTER COLUMN [' + c.column_name + '] ' + 
'NVARCHAR(' +
CASE WHEN CHARACTER_MAXIMUM_LENGTH <= 4000 THEN 
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN
'MAX' 
ELSE 
CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) 
END 
ELSE 
'MAX' 
END 
+ ')' +
CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END + '; ' + 
'ALTER TABLE '+ c.table_name + ' ADD CONSTRAINT ' + d.name +' DEFAULT '+ c.column_default + ' FOR ' + c.column_name + ';'
ELSE
'ALTER TABLE [' + c.table_schema + '].[' + c.table_name + '] ALTER COLUMN [' + c.column_name + '] ' +
'NVARCHAR(' +
CASE WHEN CHARACTER_MAXIMUM_LENGTH<=4000 THEN
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN
'MAX' 
ELSE 
CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) 
END 
ELSE 
'MAX' 
END
+ ')' +
CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END 
END,d.name, c.*
FROM information_schema.columns c
LEFT OUTER JOIN sys.default_constraints d ON d.parent_object_id = object_id(c.table_name)
AND d.parent_column_id = columnproperty(object_id(c.table_name), c.column_name, 'ColumnId')
WHERE c.data_type='VARCHAR' 
ORDER BY CHARACTER_MAXIMUM_LENGTH DESC

InformationsquelleAutor hobwell

Vous devez vous connecter pour publier un commentaire.