SQL SERVER - sp_MSforeachtable DROP TABLE

**bobobo7569** · 21/01/2012, 16h26

Bonjour,

Sous sql server 2008, je souhaite automatiser la suppression de certaines table ne commençant pas par 'SAVE' en SQL.

Je souhaite tester si la suppression des 20 tables se passe bien si c'est le cas j'applique la suppression de toutes les tables sinon je souhaite qu'aucune table ne soient supprimées.

Et je souhaite ne plus enregistrer dans une table les tables supprimées.

A priori je pense utilisé TRY CATCH et MSforeachtabl. Mais je ne sais pas si la condition : l'ensemble des tables sont supprimé ou aucune si ça se passe mal est remplie.

Je souhaite également que le code soit optimisé. J'ai fait rapisement le code suivant qui me parait très lourd.

Si quelqu'un a une idée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
BEGIN TRY
BEGIN TRANSACTION;
 
 
EXEC @DatabaseName.sys.sp_MSforeachtable ' IF PARSENAME(''?'', 3) NOT IN (''bd1'',''bd2'') AND OBJECT_NAME(''?'') NOT LIKE ('SAVE%')
BEGIN
 
INSERT TABLE DROP_TABLE (database_name, table_id, table_name, row_count)
VALUES (PARSENAME(''?'', 3),OBJECT_ID(''?''),OBJECT_NAME(''?''), SELECT(count(*) FROM (''?'')))
 
DROP TABLE @DatabaseName.dbo.?
 
END'
COMMIT TRANSACTION;
 
 
END TRY
BEGIN CATCH
 
SET @error = 1
@vErrorNumber = ERROR_NUMBER() AS ErrorNumber,
@vErrorSeverity = ERROR_SEVERITY() AS ErrorSeverity,
@vErrorState = ERROR_STATE() as ErrorState,
@vErrorProcedure = ERROR_PROCEDURE() as ErrorProcedure,
@vErrorLine = ERROR_LINE() as ErrorLine,
@vErrorMessage = ERROR_MESSAGE() as ErrorMessage;
 
ROLLBACK TRANSACTION;
 
 
END CATCH

**elsuket** · 22/01/2012, 18h01

Bonjour,

Il faut en fait savoir que l'on peut passer plusieurs noms d'objets à supprimer à une instruction DROP, et notamment à l'instruction DROP TABLE.

Vous pouvez donc utiliser les vues de catalogue pour faire cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @sql nvarchar(max)
 
SELECT		@sql = CASE
				WHEN @sql IS NULL THEN 'DROP TABLE ' + S.name + '.' + T.name
				ELSE @sql + ', ' + S.name + '.' + T.name
		END
FROM		sys.tables AS T
INNER JOIN	sys.schemas AS S
			ON T.schema_id = S.schema_id
WHERE		T.name LIKE 'SAVE%'
AND		S.name = 'dbo'
 
EXEC sp_executesql @sql

Pour journaliser les tables supprimées, il suffit d'écrire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT	INTO dbo.myDroppedTables
(
	, table_object_id
	, table_name
	, table_row_count bigint
)
SELECT		, T.object_id
		, T.name
		, SUM(PS.row_count) AS total_row_count
FROM		sys.tables AS T
INNER JOIN	sys.schemas AS S
			ON T.schema_id = S.schema_id
INNER JOIN	sys.dm_db_partition_stats AS PS
			ON PS.object_id = T.object_id
WHERE		T.name LIKE 'SAVE%'
AND		S.name = 'dbo'
GROUP BY	T.object_id
		, T.name

Si vous avez besoin de connaître le nombre exact de lignes que contient la table au moment de sa suppression, il vous faudra ajouter un DBCC UPDATEUSAGE avant l'étape de journalisation.
Cela peut se faire sur le même principe que le SELECT juste au-dessus de ce paragraphe.
Ne faites jamais un SELECT COUNT(*) pour connaître le nombre de lignes d'une table.
Si vous êtes flemmard comme moi et que vous cherchez le nombre de lignes pour une table, utilisez sp_spaceused.
Si vous cherchez le nombre de lignes de plusieurs tables, utilisez ce script

Voici donc la procédure stockée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE PROCEDURE usp_SAVE_tables_cleanup
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @error_code int
		, @error_message nvarchar(2048)
		, @sql nvarchar(max)
 
	DECLARE @SAVE_tables TABLE
	(
		table_object_id int NOT NULL
		, table_name sysname
		, table_row_count bigint NOT NULL	
	)
 
	INSERT	INTO @SAVE_tables
	(
		table_object_id
		, table_name
		, table_row_count
	)
	SELECT		T.object_id
			, T.name
			, SUM(PS.row_count) AS total_row_count
	FROM		sys.tables AS T
	INNER JOIN	sys.schemas AS S
				ON T.schema_id = S.schema_id
	INNER JOIN	sys.dm_db_partition_stats AS PS
				ON PS.object_id = T.object_id
	WHERE		T.name LIKE 'SAVE%'
	AND		S.name = 'dbo'
	GROUP BY	T.object_id
			, T.name
 
	SELECT		@sql = CASE
					WHEN @sql IS NULL THEN 'DROP TABLE ' + S.name + '.' + T.name
					ELSE @sql + ', ' + S.name + '.' + T.name
			END
	FROM		sys.tables AS T
	INNER JOIN	sys.schemas AS S
				ON T.schema_id = S.schema_id
	WHERE		T.name LIKE 'SAVE%'
	AND		S.name = 'dbo'
 
 
	BEGIN TRY
		BEGIN TRANSACTION
			EXEC sp_executesql 'DROP TABLE test, tutu'
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT	@error_code = ERROR_NUMBER()
			, @error_message = ERROR_MESSAGE()
 
		ROLLBACK TRANSACTION
	END CATCH
 
	IF @error_code IS NULL
	BEGIN
		INSERT	INTO dbo.myDroppedTables
		(
			droppped_date
			table_object_id
			, table_name
			, table_row_count
		)
		SELECT	GETDATE()
			, table_object_id
			, table_name
			, table_row_count
		FROM	@SAVE_tables
	END
	ELSE
	BEGIN
		RAISERROR(@error_message, 16, 1)
		RETURN
	END
END

@++

**bobobo7569** · 22/01/2012, 23h33

Bonsoir et merci elsuket. je préfére effectivement passer par des fonctions sql que du dev.

- la fonction : DBCC UPDATEUSAGE (AdventureWorks2008R2). Est ce que je peux l'appliquer à toutes les bases de données en un seul script ?

sp_MSForEachDb 'DBCC UPDATEUSAGE(?)'

Est ce que je dois mettre WITH COUNT_ROWS ? pour le nombre de lignes ?

**elsuket** · 23/01/2012, 16h04

- la fonction : DBCC UPDATEUSAGE (AdventureWorks2008R2). Est ce que je peux l'appliquer à toutes les bases de données en un seul script ?

Oui, sur le même modèle que ce que je vous ai proposé dans ma précédente réponse, et sans utiliser sp_MSForEachDb :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @sql nvarchar(max)
 
SELECT	@sql = CASE
			WHEN @sql IS NULL THEN 'DBCC UPDATEUSAGE ('
			ELSE @sql + '; DBCC UPDATEUSAGE ('
		END + name + ') WITH COUNT_ROWS'
FROM	sys.databases AS D
WHERE	name NOT IN ('master', 'msdb', 'tempdb', 'model', 'distribution') -- la base de données n'est pas une base de données système
AND	state_desc = 'ONLINE' -- la base de données n'est pas hors-ligne
AND	user_access_desc = 'MULTI_USER' -- la base de données n'est pas en maintenance
AND	source_database_id IS NULL -- la base de données n'est pas une capture instatanée de base de données
 
EXEC sp_executesql @sql

Est ce que je dois mettre WITH COUNT_ROWS ? pour le nombre de lignes ?

Réponse dans le script

En tout cas ça fait plaisir de voir que vous avez lu la documentation, car peu de participants à ce forum le font

Attention cependant, car si les bases de données utilisateur sont volumineuses, l'exécution de DBCC UPDATEUSAGE peut prendre longtemps.
Dans ce cas je vous conseille de vous limiter à certaines tables.

Je viens de m'apercevoir que je vous ai donné une coquille dans le code.
J'ai mis partout :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part