Générer le script de mise à jour des types de données de colonnes dont le type est déprécié

Voici plusieurs scripts qui permettent d’automatiser le changement de types de données dépréciés text, ntext et image depuis SQL Server 2005, qui les a respectivement remplacés par varchar(max), nvarchar(max), et varbinary(max), et a rendu les valeurs stockées sous de tels types bien plus malléables (utilisations de fonctions de chaîne notamment)

Si vous n’avez qu’un petit nombre de colonnes dont le type est déprécié, et/ou que toutes vos bases de données sont toute a la même version (si on a plusieurs clients par exemple, ce n’est pas toujours le cas), il suffit d’exécuter :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

------------------------------
-- Nicolas Souquet - 21-03-2011
-------------------------------
SET NOCOUNT ON
GO

DECLARE @sql varchar(max)
, @cr char(2) = CHAR(13) + CHAR(10)
, @tab char(1) = CHAR(9)

SELECT @sql = CASE
WHEN @sql IS NULL THEN '-- Number of rows for ' + T.name + ' : ' + CAST(PS.row_count AS varchar(19)) + @cr
+ 'ALTER TABLE ' + T.name + @cr
+ 'ALTER COLUMN ' + C.name + ' '
+ CASE TY.name
WHEN 'text' THEN 'varchar(max)'
WHEN 'ntext' THEN 'nvarchar(max)'
WHEN 'image' THEN 'varbinary(max)'
END
+ CASE C.is_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
ELSE @sql + @cr + '-- Number of rows for ' + T.name + ' : ' + CAST(PS.row_count AS varchar(19)) + @cr
+ 'ALTER TABLE ' + T.name + @cr
+ 'ALTER COLUMN ' + C.name + ' '
+ CASE TY.name
WHEN 'text' THEN 'varchar(max)'
WHEN 'ntext' THEN 'nvarchar(max)'
WHEN 'image' THEN 'varbinary(max)'
END
+ CASE C.is_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
END + @cr + 'GO' + @cr
FROM sys.schemas AS S
INNER JOIN sys.tables AS T
ON S.schema_id = T.schema_id
INNER JOIN sys.dm_db_partition_stats AS PS
ON PS.object_id = T.object_id
INNER JOIN sys.columns AS C
ON C.object_id = T.object_id
INNER JOIN sys.types AS TY
ON TY.system_type_id = C.system_type_id
AND TY.system_type_id = TY.user_type_id
WHERE S.name = 'dbo'
AND TY.name IN ('image', 'text', 'ntext')
AND PS.index_id IN (0, 1) -- Heap or Clustered index
ORDER BY T.name, C.column_id

PRINT @sql

Comme PRINT est limité à afficher seulement les 8000 premiers caractères d’une chaîne, il se peut que le script que vous obtenez soit incomplet.
Dans ce cas une boucle devient nécessaire, pour n’afficher qu’une instruction par colonne dont le type est à changer :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

------------------------------
-- Nicolas Souquet - 21-03-2011
-------------------------------
DECLARE @tables_with_deprecated_type TABLE
(
table_name sysname
, column_name sysname
, column_data_type_name sysname
, is_column_nullable bit
, table_row_count bigint
)

INSERT @tables_with_deprecated_type
SELECT T.name
, C.name
, TY.name
, C.is_nullable
, PS.row_count
FROM sys.schemas AS S
INNER JOIN sys.tables AS T
ON S.schema_id = T.schema_id
INNER JOIN sys.dm_db_partition_stats AS PS
ON PS.object_id = T.object_id
INNER JOIN sys.columns AS C
ON C.object_id = T.object_id
INNER JOIN sys.types AS TY
ON TY.system_type_id = C.system_type_id
AND TY.system_type_id = TY.user_type_id
WHERE S.name = 'dbo'
AND TY.name IN ('image', 'text', 'ntext')
AND PS.index_id IN (0, 1) -- Heap or Clustered index
ORDER BY T.name, C.column_id

DECLARE @table_name sysname
, @column_name sysname
, @column_data_type_name sysname
, @is_column_nullable bit
, @table_row_count bigint
---
, @sql varchar(max)
, @cr char(2) = CHAR(13) + CHAR(10)
, @tab char(1) = CHAR(9)

WHILE EXISTS
(
SELECT *
FROM @tables_with_deprecated_type
)
BEGIN
SELECT TOP (1) @table_name = table_name
, @column_name = column_name
, @column_data_type_name = column_data_type_name
, @is_column_nullable = is_column_nullable
, @table_row_count = table_row_count
FROM @tables_with_deprecated_type

SET @sql = '-- Number of rows for ' + @table_name + ' : ' + CAST(@table_row_count AS varchar(20)) + @cr
+ 'ALTER TABLE ' + @table_name + @cr
+ 'ALTER COLUMN ' + @column_name + ' '
+ CASE @column_data_type_name
WHEN 'text' THEN 'varchar(max)'
WHEN 'ntext' THEN 'nvarchar(max)'
WHEN 'image' THEN 'varbinary(max)'
END
+ CASE @is_column_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END + @cr + 'GO' + @cr

PRINT @sql

DELETE TOP (1) @tables_with_deprecated_type
END

Si en revanche on cherche à créer un tel script pour des bases de données dont le schéma est à des stades d’évolutions différents (par exemple si l’on a des clients qui n’utilisent pas tous la même version de l’application, et donc de la base de données), on peut alors tester l’existence de la colonne et de son type déprécié.
Cela permet d’utiliser le script généré sans ce soucier de l’existence ou non de la colonne, et/ou de son type.

Comme précédemment, si l’on a un petit nombre de colonnes, le script suivant fonctionne (le but étant de ne pas dépasser la limite des 8000 caractères retournés par PRINT, comme décrit plus haut) :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

------------------------------
-- Nicolas Souquet - 21-03-2011
-------------------------------
DECLARE @sql varchar(max)
, @cr char(2) = CHAR(13) + CHAR(10)
, @tab char(1) = CHAR(9)

SELECT @sql = CASE
WHEN @sql IS NULL THEN '-- Number of rows for ' + T.name + ' : ' + CAST(PS.row_count AS varchar(50)) + @cr
+ 'IF EXISTS' + @cr +
+ '(' + @cr
+ @tab + 'SELECT' + @tab + @tab + '*' + @cr
+ @tab + 'FROM' + @tab + @tab + 'sys.tables AS T' + @cr
+ @tab + 'INNER JOIN' + @tab + 'sys.columns AS C ON T.object_id = C.object_id' + @cr
+ @tab + 'INNER JOIN' + @tab + 'sys.types AS TY ON TY.system_type_id = C.system_type_id' + @cr
+ @tab + 'WHERE' + @tab + @tab + 'T.name = ''' + T.name + '''' + @cr
+ @tab + 'AND' + @tab + @tab + 'C.name = ''' + C.name + '''' + @cr
+ @tab + 'AND' + @tab + @tab + 'TY.name = ''' + TY.name + '''' + @cr
+ ')' + @cr +
'ALTER TABLE ' + T.name + @cr
+ 'ALTER COLUMN ' + C.name + ' '
+ CASE TY.name
WHEN 'text' THEN 'varchar(max)'
WHEN 'ntext' THEN 'nvarchar(max)'
WHEN 'image' THEN 'varbinary(max)'
END
+ CASE C.is_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
ELSE @sql + @cr + '-- Number of rows for ' + T.name + ' : ' + CAST(PS.row_count AS varchar(50)) + @cr
+ 'IF EXISTS' + @cr +
+ '(' + @cr
+ @tab + 'SELECT' + @tab + @tab + '*' + @cr
+ @tab + 'FROM' + @tab + @tab + 'sys.tables AS T' + @cr
+ @tab + 'INNER JOIN' + @tab + 'sys.columns AS C ON T.object_id = C.object_id' + @cr
+ @tab + 'INNER JOIN' + @tab + 'sys.types AS TY ON TY.system_type_id = C.system_type_id' + @cr
+ @tab + 'WHERE' + @tab + @tab + 'T.name = ''' + T.name + '''' + @cr
+ @tab + 'AND' + @tab + @tab + 'C.name = ''' + C.name + '''' + @cr
+ @tab + 'AND' + @tab + @tab + 'TY.name = ''' + TY.name + '''' + @cr
+ ')' + @cr +
'ALTER TABLE ' + T.name + @cr
+ 'ALTER COLUMN ' + C.name + ' '
+ CASE TY.name
WHEN 'text' THEN 'varchar(max)'
WHEN 'ntext' THEN 'nvarchar(max)'
WHEN 'image' THEN 'varbinary(max)'
END
+ CASE C.is_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
END + @cr + 'GO' + @cr + @cr
FROM sys.schemas AS S
INNER JOIN sys.tables AS T
ON S.schema_id = T.schema_id
INNER JOIN sys.dm_db_partition_stats AS PS
ON PS.object_id = T.object_id
INNER JOIN sys.columns AS C
ON C.object_id = T.object_id
INNER JOIN sys.types AS TY
ON TY.system_type_id = C.system_type_id
AND TY.system_type_id = TY.user_type_id
WHERE 1 = 1
AND S.name = 'dbo'
AND TY.name IN ('image', 'text', 'ntext')
ORDER BY T.name, C.column_id
GO

Dans le cas contraire, on peut recourir, de façon similaire, à une boucle :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

------------------------------
-- Nicolas Souquet - 21-03-2011
-------------------------------
SET NOCOUNT ON
GO

DECLARE @tables_with_deprecated_type TABLE
(
table_name sysname
, column_name sysname
, column_data_type_name sysname
, is_column_nullable bit
, table_row_count bigint
)

INSERT @tables_with_deprecated_type
SELECT T.name
, C.name
, TY.name
, C.is_nullable
, PS.row_count
FROM sys.schemas AS S
INNER JOIN sys.tables AS T
ON S.schema_id = T.schema_id
INNER JOIN sys.dm_db_partition_stats AS PS
ON PS.object_id = T.object_id
INNER JOIN sys.columns AS C
ON C.object_id = T.object_id
INNER JOIN sys.types AS TY
ON TY.system_type_id = C.system_type_id
AND TY.system_type_id = TY.user_type_id
WHERE 1 = 1
AND S.name = 'dbo'
AND TY.name IN ('image', 'text', 'ntext')
AND PS.index_id IN (0, 1) -- Heap or Clustered index
ORDER BY T.name, C.column_id

DECLARE @table_name sysname
, @column_name sysname
, @column_data_type_name sysname
, @is_column_nullable bit
, @table_row_count bigint
---
, @sql varchar(max)
, @cr char(2) = CHAR(13) + CHAR(10)
, @tab char(1) = CHAR(9)

WHILE EXISTS
(
SELECT *
FROM @tables_with_deprecated_type
)
BEGIN
SELECT TOP (1) @table_name = table_name
, @column_name = column_name
, @column_data_type_name = column_data_type_name
, @is_column_nullable = is_column_nullable
, @table_row_count = table_row_count
FROM @tables_with_deprecated_type

SET @sql = '-- Number of rows for ' + @table_name + ' : ' + CAST(@table_row_count AS varchar(20)) + @cr
+ 'IF EXISTS' + @cr +
+ '(' + @cr
+ @tab + 'SELECT' + @tab + @tab + '*' + @cr
+ @tab + 'FROM' + @tab + @tab + 'sys.tables AS T' + @cr
+ @tab + 'INNER JOIN' + @tab + 'sys.columns AS C ON T.object_id = C.object_id' + @cr
+ @tab + 'INNER JOIN' + @tab + 'sys.types AS TY ON TY.system_type_id = C.system_type_id' + @cr
+ @tab + 'WHERE' + @tab + @tab + 'T.name = ''' +@table_name + '''' + @cr
+ @tab + 'AND' + @tab + @tab + 'C.name = ''' + @column_name + '''' + @cr
+ @tab + 'AND' + @tab + @tab + 'TY.name = ''' + @column_data_type_name + '''' + @cr
+ ')' + @cr +
'ALTER TABLE ' + @table_name + @cr
+ 'ALTER COLUMN ' + @column_name + ' '
+ CASE @column_data_type_name
WHEN 'text' THEN 'varchar(max)'
WHEN 'ntext' THEN 'nvarchar(max)'
WHEN 'image' THEN 'varbinary(max)'
END
+ CASE @is_column_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END + @cr + 'GO' + @cr

PRINT @sql

DELETE TOP (1) @tables_with_deprecated_type
END
GO

Le nombre de lignes de la table est imprimé dans tous les cas, et pour les tables volumineuses, on exécutera la changement de type avec précaution !

Bonne mise à jour !

ElSüket

Une réflexion au sujet de « Générer le script de mise à jour des types de données de colonnes dont le type est déprécié »

Le 22 mars 2011 à 15 h 49 min, zinzineti a dit :

C’est bien de le souligner en bas du billet : « on exécutera la changement de type avec précaution ! »
Parce qu’on ne sait pas tout ce que les applications (pour ne pas dire les développeurs) font…

–> Automatiser cette tâche sur un serveur de PROD me paraît un peu risqué si on n’a pas sous la main le script de retour arrière.
je m’explique :
S’il y a juste deux colonnes qui sont MAJ pas de problème pour faire un retour arrière rapide AU CAS OU !
Mais imaginons le cas où il y a plusieurs dizaines de colonnes qui sont MAJ…en cas de problème
au niveau d’une application par exemple , il faut prévoir le script de retour arrière

–> je propose de :
1.
Lister d’abord les colonnes concernées
—————-
SELECT OBJECT_NAME(c.OBJECT_ID) TableName
, c.name ColonneName
,t.name TypeName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name IN (‘text’,’ntext’,’image’)
ORDER BY TableName;

2.
Préparer le script de retour arrière (pour annuler l’opération de MAJ)
—————-

3.
–> Aplliquer la MAJ

Question
——–
Je me demande si les types ‘timestamp’ et ‘datetime’ ne sont pas aussi dépréciés sous SQL Server 2008 ?

A +

Connectez-vous pour répondre

Laisser un commentaire Annuler la réponse.

Vous devez être connecté(e) pour rédiger un commentaire.

Identifiez-vous

Créer un compte

Le Blog SQL Server d'ElSüket

Nicolas Souquet – Expert SQL Server

Générer le script de mise à jour des types de données de colonnes dont le type est déprécié

Une réflexion au sujet de « Générer le script de mise à jour des types de données de colonnes dont le type est déprécié »

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?