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
|
-- declaration de variables
DECLARE @joker varchar(128);
DECLARE @tablename varchar(128);
DECLARE @colname varchar(128);
DECLARE @minseqval bigint;
DECLARE @lastvalue bigint;
DECLARE @tablenameref varchar(128);
DECLARE @colnameref varchar(128);
-- variables de type 'table'
DECLARE @fkreftable TABLE (tablename varchar(128), colname varchar(128));
-- Initialisation du Joker (motif) - ex: commence par 'T_'
SET @joker = 'T_'
-- creation tables
IF OBJECT_ID('tempdb..#ident', 'U') IS NOT NULL
DROP TABLE #ident
--GO
CREATE TABLE #ident(tablename varchar(128), colname varchar(128), seqname varchar(128), minseqval bigint, lastvalue bigint);
-- ENREGISTREMENT des pk à partir des sequences de la base, dans la table '#ident'
INSERT INTO #ident(tablename, colname)
SELECT ...;
-- desactivation des contraintes des tables
exec sp_MSforeachtable
'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
DECLARE curseur_seq CURSOR FOR
SELECT tablename, colname FROM #ident;
-- DEALLOCATE curseur_ref;
DECLARE curseur_ref CURSOR FAST_FORWARD FOR
SELECT tablename, colname FROM @fkreftable;
OPEN curseur_seq;
FETCH curseur_seq INTO @tablename, @colname;
-- parcours de la table des sequences
WHILE @@FETCH_STATUS = 0
BEGIN
-- récupération du min de la séquence
UPDATE #ident
SET @minseqval = (SELECT MIN(@colname) FROM OBJECT_ID(@tablename, 'U') )
WHERE tablename = @tablename AND colname = @colname;
-- récupération du max (= lastvalue) de la séquence
UPDATE #ident
SET @lastvalue = (SELECT MAX(@colname) FROM OBJECT_ID(@tablename, 'U') )
WHERE tablename = @tablename AND colname = @colname;
-- mise à jour des valeurs de la clef primaire
UPDATE OBJECT_ID(@tablename, 'U') SET @colname = @colname - @minseqval + 1;
-- mise à jour de 'last value' de la sequence
-- DBCC CHECKIDENT ("@tablename", RESEED, @lastvalue );
-- recuperation des clefs etrangeres qui referencent la sequence dans la table '@fkreftable'
INSERT INTO @fkreftable("tablename", "colname")
SELECT OBJECT_NAME (f.referenced_object_id), COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = @tablename
AND COL_NAME(fc.parent_object_id, fc.parent_column_id) = @colname;
-- mise à jour les valeurs des foreign keys
OPEN curseur_ref;
FETCH curseur_ref INTO @tablenameref, @colnameref
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE OBJECT_ID(@tablenameref, 'U') SET @colnameref = @colnameref - @minseqval + 1;
FETCH curseur_ref INTO @tablenameref, @colnameref;
END
CLOSE curseur_ref;
DEALLOCATE curseur_ref;
-- reinitialisation table temporaire
DELETE FROM @fkreftable;
FETCH curseur_seq INTO @tablename, @colname;
END;
CLOSE curseur_seq;
DEALLOCATE curseur_seq;
-- reactivation des contraintes des tables
exec sp_MSforeachtable
'ALTER TABLE ? CHECK CONSTRAINT ALL';
GO |
Partager