Discussion :

[Dekdek3578]

Bonjours à tous,
Voilà j'ai un petit problème.
J'aimerais bien remettre à zéro ma base de donnée sans avoir besoin de faire un "drop and créate" des tables.

J'ai une Bdd avec 200 tables et des PK,FK dans tous les sens.
La solution un peu brutale que j'utilise à l'heure actuel est la suivante :

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
 
-- Suppression de toutes les FK
 
DECLARE @NomTable as varchar(50)
 
DECLARE curNomTable CURSOR  FOR
SELECT NAME 
FROM SYSOBJECTS 
WHERE TYPE = 'U'
FOR READ ONLY
 
OPEN curNomTable
 
FETCH NEXT FROM curNomTable INTO @NomTable 
WHILE (@@fetch_status = 0) BEGIN
 
	EXEC ('TRUNCATE TABLE ' + @NomTable)
 
	FETCH NEXT FROM curNomTable INTO @NomTable
END
 
 
-- Création de toutes les FK supprimées

Avez vous une solution un peu plus propre? et surtout qui serait optimisé peu importe le nombre de table et FK?
Car mon modèle risque de changer bientôt et j'aimerais éviter de devoir tout le temps modifier le script de RAZ.

Merci pour votre aide.


EDIT : Je tiens juste à préciser qu'il n'y a aucun on delete cascade sur mes clef primaire d'où mon problème

[StringBuilder]

Dans les FK, il faut stipuler "cascade on delete"

Ensuite, t'as juste à vider les tables de référence, et normalement, ça se propage à toutes les autres tables.

Attention cependant à ne pas saturer tempdb et le journal des transactions !

[Dekdek3578]

hihi c'est bien pour ca que j'avais préciser dans l'edit qu'il n'y avait pas le "on delte cascade" car je connais bien se fonctionnement mais pas les créateur de la bdd à l'époque...

Je les rajouterais quand j'aurais le temps de repasser sur toute la base...

Pour ceux que ma méthode bourrin pourraient intéresser :

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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
SET NOCOUNT ON
 
 
 
/*
* Procéssus de purge : 
* 1 - On met en mémoire dans une variable de type table les FK de la base
* 2 - On supprime toutes les FK de la base
* 3 - On parcoure l'ensemble des tables et on les RAZ
* 4 - On Récupère les FK mises en mémoire pour les recréer
*/
 
-- 1 - On met en mémoire dans une variable de type table les FK de la base
DECLARE @ListeFK table (fk_id INT,
                        DebutCreate VARCHAR(MAX),
                        MilieuCreate VARCHAR(MAX),
                        FinCreate VARCHAR(MAX),
                        ListeColonnesParent VARCHAR(MAX),
                        ListeColonnesRef VARCHAR(MAX))
 
DECLARE @fk_id INT
DECLARE @ListeFKColonnesParent VARCHAR(MAX)
DECLARE @ListeFKColonnesRef VARCHAR(MAX)
DECLARE @req VARCHAR(MAX)
 
INSERT INTO @ListeFK(fk_id,DebutCreate,MilieuCreate,FinCreate)
SELECT fk.object_id,
    'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WITH ' + CASE WHEN fk.is_not_trusted=1 THEN 'NOCHECK' ELSE 'CHECK' END + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (',
    ') REFERENCES ' + QUOTENAME(s_r.name) + '.' + QUOTENAME(t_r.name) + ' (',
    ') ON DELETE ' + REPLACE(fk.delete_referential_action_desc,'_',' ') + ' ON UPDATE ' + REPLACE(fk.update_referential_action_desc,'_',' ')
        + CASE WHEN fk.is_not_for_replication=1 THEN ' NOT FOR REPLICATION' ELSE '' END
        + ';' + CASE WHEN fk.is_disabled=1 THEN 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' NOCHECK CONSTRAINT ' + QUOTENAME(fk.name) + ';' ELSE '' END
FROM sys.foreign_keys fk
JOIN sys.tables t on fk.parent_object_id=t.object_id
JOIN sys.schemas s on s.schema_id=t.schema_id
JOIN sys.tables t_r on fk.referenced_object_id=t_r.object_id
JOIN sys.schemas s_r on s_r.schema_id=t_r.schema_id
 
-- Pour chaque FK
DECLARE curListFK CURSOR FOR
SELECT fk_id
FROM @ListeFK
 
OPEN curListFK
FETCH NEXT FROM curListFK INTO @fk_id
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construction de la liste des colonnes de la table parente
    SELECT @ListeFKColonnesParent=','
    SELECT @ListeFKColonnesParent+=quotename(c.name)+','
    FROM sys.foreign_key_columns fkc
        JOIN sys.columns c ON c.object_id=fkc.parent_object_id
                            AND c.column_id=fkc.parent_column_id
    WHERE fkc.constraint_object_id=@fk_id
    ORDER BY fkc.constraint_column_id
 
    -- Construction de la liste des colonnes de la table référencée
    SELECT @ListeFKColonnesRef=','
    SELECT @ListeFKColonnesRef+=quotename(c.name)+','
    FROM sys.foreign_key_columns fkc
        JOIN sys.columns c ON c.object_id=fkc.referenced_object_id
                            AND c.column_id=fkc.referenced_column_id
    WHERE fkc.constraint_object_id=@fk_id
    ORDER BY fkc.constraint_column_id
 
    -- Mise en forme des listes de colonnes
    SELECT @ListeFKColonnesParent=SUBSTRING(@ListeFKColonnesParent,2,LEN(@ListeFKColonnesParent)-2)
    SELECT @ListeFKColonnesRef=SUBSTRING(@ListeFKColonnesRef,2,LEN(@ListeFKColonnesRef)-2)
 
	-- MAJ de la table contenant la liste des FK
    UPDATE @ListeFK
    SET ListeColonnesParent=@ListeFKColonnesParent,
        ListeColonnesRef=@ListeFKColonnesRef
    WHERE fk_id=@fk_id
 
    FETCH NEXT FROM curListFK INTO  @fk_id
END
 
CLOSE curListFK
DEALLOCATE curListFK
 
 
 
-- 2 - On supprime toutes les FK de la base
DECLARE cursREQDelFK CURSOR FOR
SELECT 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name)+ ';'
FROM sys.foreign_keys fk
JOIN sys.tables t ON fk.parent_object_id=t.object_id
JOIN sys.schemas s ON s.schema_id=t.schema_id
 
 
OPEN cursREQDelFK
FETCH NEXT FROM cursREQDelFK INTO @req
 
WHILE @@FETCH_STATUS = 0
BEGIN
	exec( @req)
 
    FETCH NEXT FROM cursREQDelFK INTO  @req
END
 
CLOSE cursREQDelFK
DEALLOCATE cursREQDelFK
 
 
-- 3 - On parcoure l'ensemble des tables et on les RAZ
DECLARE @NomTable as varchar(50)
 
DECLARE curNomTable CURSOR  FOR
SELECT NAME 
FROM SYSOBJECTS 
WHERE TYPE = 'U'
FOR READ ONLY
 
OPEN curNomTable
 
FETCH NEXT FROM curNomTable INTO @NomTable 
WHILE (@@fetch_status = 0) BEGIN
 
	EXEC ('TRUNCATE TABLE ' + @NomTable)
 
	FETCH NEXT FROM curNomTable INTO @NomTable
END
 
CLOSE curNomTable
DEALLOCATE curNomTable
 
 
-- 4 - On Récupère les FK mises en mémoire pour les recréer
DECLARE cursREQCreateFK CURSOR FOR
SELECT DebutCreate+ListeColonnesParent+MilieuCreate+ListeColonnesRef+FinCreate
FROM @ListeFK
 
OPEN cursREQCreateFK
FETCH NEXT FROM cursREQCreateFK INTO @req
 
WHILE @@FETCH_STATUS = 0
BEGIN
	exec( @req)
 
    FETCH NEXT FROM cursREQCreateFK INTO  @req
END
 
CLOSE cursREQCreateFK
DEALLOCATE cursREQCreateFK

PS : Merci quand même pour la réponse