Ajouter une colonne dans plusieurs tables a la fois [Résolu]

[NicaeaCivitas]

Bonjour,

je cherche a faire un script me permettant d'insérer dans une liste de tables 4 nouvelles colonnes, qui auront le meme nom (la liste de tables sera dans une table tempo et parcourue).
J'ai plusieurs centaines de tables dans ma SGBD et plusieurs dizaines seront modifiées, d'ou la nécessité d'un script.
Est-ce possible de faire cela et si oui, comment procéder car la je bute un peu ?

Merci.

[elsuket]

Bonjour,

Vous pouvez faire comme suit :

Code :

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
SET NOCOUNT ON
GO
 
DECLARE @liste_table TABLE
(
	table_name sysname
)
 
DECLARE @colonnes_a_ajouter TABLE
(
	column_type_and_nullability nvarchar(256)
)
 
INSERT	INTO @liste_table (table_name) VALUES ('maTable1'), ('maTable2'), ('maTable3'), ('maTable4')
INSERT	INTO @colonnes_a_ajouter VALUES ('colonne1 int NOT NULL'), ('colonne2 datetime'), ('colonne3 varchar(20)'), ('colonne4 bit NOT NULL')
 
DECLARE @sql varchar(max)
 
SELECT		@sql = CASE
				WHEN @sql IS NULL THEN 'ALTER TABLE ' + T.table_name + ' ADD ' + C.column_type_and_nullability
				ELSE @sql + 'ALTER TABLE ' + T.table_name + ' ADD ' + C.column_type_and_nullability
		END + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM		@liste_table AS T
CROSS JOIN	@colonnes_a_ajouter AS C
 
PRINT	@sql
--EXEC	(@sql)

Notez au passage que l'on peut ajouter des colonnes NOT NULL uniquement aux tables qui n'ont encore aucune ligne.
Dans le cas contraire, vous devrez ajouter le colonne, puis la mettre à jour avec des valeurs, et ensuite la rendre non-NULLable.
Une alternative est d'ajouter une contrainte de valeur par défaut, qui fait le travail pour vous mais met toute la colonne à la valeur par défaut.
Vous pouvez spécifier une telle contrainte dans la colonne column_type_and_nullability.

Voici une autre écriture, que vous pouvez employer sous SQL Server 2005 ou 2008 :

Code :

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
DECLARE @sql varchar(max)
 
;WITH
	CTE_TABLES AS
	(
		SELECT 'maTable1' AS table_name
		UNION ALL SELECT 'maTable2'
		UNION ALL SELECT 'maTable3'
		UNION ALL SELECT 'maTable4'
	)
	, CTE_COLONNES AS
	(
		SELECT 'colonne1 int NOT NULL' AS column_type_and_nullability
		UNION ALL SELECT 'colonne2 datetime'
		UNION ALL SELECT 'colonne3 varchar(20)'
		UNION ALL SELECT 'colonne4 bit NOT NULL'
	)
SELECT		@sql = CASE
				WHEN @sql IS NULL THEN 'ALTER TABLE ' + T.table_name + ' ADD ' + C.column_type_and_nullability
				ELSE @sql + 'ALTER TABLE ' + T.table_name + ' ADD ' + C.column_type_and_nullability
		END + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM		CTE_TABLES AS T
CROSS JOIN	CTE_COLONNES AS C
 
PRINT	@sql
--EXEC	(@sql)

Vous pouvez choisir de dé-commenter le EXEC (@sql), ou bien de générer le code et de l'exécuter tranquillement à la main dans une fenêtre de requête.

@++

[NicaeaCivitas]

Merci a toi, tu m'as bien aidé !