Remplacer les colonnes ayant des types obsolètes

Laisser une réponse

SQL Server évoluant, les types de données de la version 2000 seront de moins en moins supportés. Certains sont considérés comme obsolète (ils ne fonctionneront plus dans une version future) et d’autres inadéquat (TIMESTAMP, SMALLDATETIME, MONEY, DATETIME…). Voici une méthode pour les remplacer tous dans toutes vos tables de la base courante.

Le tableau ci dessous montre quels types doivent être changés (OLD) et par quel autre type (NEW) :

OLD           NEW
------------- --------------
image         VARBINARY(max)
text          VARCHAR(max)
ntext         NVARCHAR(max)
timestamp     ROWVERSION
datetime      DATETIME2(3)
smalldatetime DATETIME2(0)
money         DECIMAL(19,4)

La requête ci dessous produit un script SQL permettant de changer l’un vers l’autre dans toutes vos tables :

WITH T AS
(SELECT N'image' AS OLD, N'VARBINARY(max)' AS NEW
 UNION ALL
 SELECT N'text', N'VARCHAR(max)'
 UNION ALL
 SELECT N'ntext', N'NVARCHAR(max)'
 UNION ALL
 SELECT N'timestamp', N'ROWVERSION'
 UNION ALL
 SELECT N'datetime', N'DATETIME2(3)'
 UNION ALL
 SELECT N'smalldatetime', N'DATETIME2(0)'
 UNION ALL
 SELECT N'money', N'DECIMAL(19,4)')
SELECT  
N'
BEGIN TRANSACTION;
BEGIN TRY
   EXEC (''ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ADD [' + COLUMN_NAME + '_tmp] ' + NEW + ';'')
   EXEC (''UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] SET [' + COLUMN_NAME + '_tmp]  = [' + COLUMN_NAME + '];'')
   EXEC (''ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP COLUMN [' + COLUMN_NAME + '];'')
   EXEC sp_rename ''[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '].[' + COLUMN_NAME + '_tmp]'', ''' + COLUMN_NAME + ''';
   COMMIT;
END TRY
BEGIN CATCH
   DECLARE @ERR NVARCHAR(1200);
   SET @ERR = ERROR_MESSAGE();
   IF XACT_STATE()  0
      ROLLBACK;
   RAISERROR(@ERR, 16, 1);
END CATCH;
GO
'            
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       INNER JOIN T
             ON C.DATA_TYPE = T.OLD;

Il n’y a plus ensuite qu’à lancer le résultat de cette requête en exécution.

* * *

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
Développez et administrez pour la performance avec SQL Server 2014

Développez et administrez pour la performance avec SQL Server 2014

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL Server

Laisser un commentaire