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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
| table cle primaire personne
{ champs
nom(champ cle primaire)
salaire }
nom salaire
louis 20000
henry 10 000
jet-li 200 000
table cle etrangeres population
{champs
Type (cle primaire)
nom (cles etrangere mais primaire dans personne)
lieu }
type nom lieu
Asiatique Jet-li coree
Africain henry ghana
europenne louis france
drop procedure PR_UPDATE_Key_References
go
--sql server data masking , replace sensitive data with some dummy data
GO
PR_UPDATE_REFERENCES 'Table_voiture','car_id' /*test avec la table'Table_voiture' et de cles primaire Car_id et */
GO
--
---this stored procedure updates our foreign key columns according to the values of our primary keey columns so we maintain a correspondance of values
CREATE procedure PR_UPDATE_Key_References(@TBL_NAME VARCHAR(256),@PK_COL_NAME VARCHAR(256),@IDENTITY_COL VARCHAR(256)='',@MASK_WORD VARCHAR(256)='')
AS
SET NOCOUNT ON
-- Adding the Identity Column
--ajoute une colonne Idendite à la table clé primaire /*1*/ et /*2*/
/*1*/select 'alter table '+ @TBL_NAME + ' add ' + @IDENTITY_COL +' INT IDENTITY(1,1)'
/*2*/select 'GO'
-- updating all the foreighn keys 1st with identity column + 'MASK'
-- will update the Primary key at the end of the script
-- !! update through join with new added identity column
print(@TBL_NAME)
print(@PK_COL_NAME )
select distinct
-- Below part will produce the script to drop All Constraints
--enleve toutes les clés etrangeres
'ALTER TABLE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' DROP CONSTRAINT ' + object_name(constid)
from
sys.sysforeignkeys A
join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME and C.NAME = @PK_COL_NAME
print(@TBL_NAME)
---------------------------------------------------------------------------------------------------------
-- Below statement will update all the foreing keys with the values in the updated primary key
select + CHAR(256) + 'UPDATE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' SET ' + B.name
+ ' = CAST('+object_name(rkeyid)
+
'.'+ @IDENTITY_COL + ' AS VARCHAR(256))' +' + '''+ @MASK_WORD +'''' +' FROM ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid)+' ' +
'JOIN '
+ SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) + ' '+
+ 'ON ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) +'.'
+C.NAME +' = '
+SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) + '.'+
B.NAME
from
sys.sysforeignkeys A
join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME and C.NAME = @PK_COL_NAME
---------------------------------------------------------------------------------------------------------
-- Below statement will update all the foreing keys with the values in the updated primary key
SELECT 'update '+ @TBL_NAME + ' set ' + @PK_COL_NAME + ' = '+@IDENTITY_COL +'+'''+ @MASK_WORD+''''
print(@TBL_NAME)
--Reajoute toutes les cles etrangeres qui ont ét" enlevee avnt
------------------------------------------------------------------------------------------------
select + CHAR(256) +
-- Below statement will produce the script to Create All Constraints which are Scripted to Dropped Before
'ALTER TABLE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' ADD CONSTRAINT ' + object_name(constid)
+' FOREIGN KEY (' + b.name
+ ') REFERENCES ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid)
+ '(' + c.name + ')'
from
sys.sysforeignkeys A
join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME and C.NAME = @PK_COL_NAME
------------------------------------------------------------------------------------------------
-- Dropping the Identity Column
print(@TBL_NAME)
select 'GO'
print(@TBL_NAME)
select 'alter table '+ @TBL_NAME + ' drop COLUMN ' + @IDENTITY_COL
print(@TBL_NAME)
Mon souci à moi c'est que quand je débugge de la ligne 1 à 66 elle affiche correctement la liste des tables cles primaires et etrangeres liees entre elles.
Mais mon deguggage total donne ca :
alter table Table_voiture add IID_TEMP INT IDENTITY(1,1)
(Aucun nom de colonne)
GO
(Aucun nom de colonne)
GO
(Aucun nom de colonne)
update Table_voiture set car_id = IID_TEMP+'MASK'
(Aucun nom de colonne)
update Table_voiture set car_id = IID_TEMP+'MASK'
Quelqun peut il maider (pour la base de test sous sql server creer des tables primaires et etrangeres (la cle primaire de la table 1 est ertangere dans la 2)
ensuite faire
use [nom base de donnes]
go
exec PR_UPDATE_Key_References 'nom de la table cle primaire','nom de la cle primaire de cette table' |
Probleme avec ma procedure stockee Mise à jour des cles primaies et etrangers
Répondre avec citation
Partager