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
|
-- Un curseur sur tous les indexes des tables "user" dans la base courante:
declare ixcsr cursor
for select name, id, indid from sysindexes
where id in (select id from sysobjects where type = 'U')
go
set nocount on
declare @name varchar(32)
, @id int
, @indid int
, @colid int
declare @cmd varchar(300)
, @tab varchar(32)
, @colname varchar(32)
, @colorder varchar(32)
open ixcsr
fetch ixcsr into @name, @id, @indid
while @@sqlstatus = 0
begin
select @tab = object_name(@id)
-- la commande de drop... celle-ci est triviale!
select @cmd = "drop index " + @tab + "." + @name
print "%1!", @cmd
-- on construit la commande de creation de facon iterative
select @colid = 1
select @cmd = "create index " + @name + " on " + @tab + "("
while @colid <= 31
begin
select @colname = index_col(@tab, @indid, @colid)
, @colorder = index_colorder(@tab, @indid, @colid)
-- si le nom est null c'est qu'on a traite toutes les colonnes de
-- l'indexe
if @colname is null
goto LAST_COLUMN
if @colid > 1
select @cmd = @cmd + ", "
select @cmd = @cmd + @colname + " " + @colorder
select @colid = @colid + 1
end
LAST_COLUMN:
select @cmd = @cmd + ")"
print "%1!", @cmd
fetch ixcsr into @name, @id, @indid
end
close ixcsr
deallocate cursor ixcsr
go |
Partager