Script permettant de créer l'ensemble des indexes d'une base

Version imprimable

01/07/2005, 13h05
matacars

Script permettant de créer l'ensemble des indexes d'une base

Bonjour à tous,

Je souhaiterais obtenir un script permettant, lorsqu'on l'applique sur une base donnée, de générer un script créant l'ensemble des indes de cette base.

L'idée est de pouvoir passer automatiquement l'ensemble des indexes d'une base sur une autre base identique mais dépourvue d'index.

Via Enterprise Manager, cette option est proposée dans le générateur de script, mais en complément du script générant les tables.

Merci à ceux qui pourront m'aider.
01/07/2005, 13h39
NoisetteProd
Salut !

Il existe une manière au sein d'Entreprise Manager d'obtenir le code SQL qui a permis de créer un index.

Pour ce faire, tu vas sur la table d'ou tu souhaites extraire le code d'un index, tu fais un clic droit "Toutes le tâches" puis "gérer les index".
Ensuite, tu choisi l'un des index de ta table, tu clique sur "modifier", et pour finir , tu clic en bas à gauche sur "Edition SQL".

Tu vois alors aparaitre le code qui a servi à créer l'index.

Il ne te reste plus qu'a faire un script global, et c'est OK ! (Il est néanmoins peut etre bon, de vérifier avant de faire le créate si l'index n'existe pas avec une instruction du genre
Code:

1 2 3 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'nom de l index') DROP INDEX Table.[Nom de l'index]
Voila, j'espère que ca peut t'aider !
A+
01/07/2005, 13h43
matacars

Salut,

Merci de m'avoir répondu.

En fait, ce n'est pas exactement ce que je cherche : ta méthode permet de générer le code pour un seul index. Or, je souhaite le faire pour l'ensemble des indexes de la base.

Je me vois difficilement générer mes 3500 indexes un par un ;-).
01/07/2005, 14h06
NoisetteProd

ca dépend, si tu as des index "type" , tu peux travailer avec des variables, tu as moins de code a effectuer.
01/07/2005, 14h17
matacars

Non, chaque table a ses indexes bien à elle.

En fait, je sais que la génération d'un script global est possible. Il suffit de travailler à partir des tables système.

Je pense que je pourrais y arriver, mais c'est une question de temps(c'est une base de prod de 900 tables qui a perdu ses scripts). Il me faudrait un tel script assez rapidement.
C'est pour ça que je demande si quelqu'un a un tel script à disposition.

Mais sinon, ce n'est pas très grave. Je vais le créer moi même.

Merci quand même ;-).

ce code permet de recuperer les trigger d'une base... Essaye de l'adapter pour les index... :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
    dbo.sysobjects.name, 
    dbo.sysobjects.xtype, 
    dbo.syscomments.text, 
    dbo.sysusers.name, 
    dbo.sysobjects.crdate 
 
FROM 
    dbo.sysobjects 
INNER JOIN dbo.syscomments 
    ON dbo.syscomments.id = dbo.sysobjects.id 
INNER JOIN dbo.sysusers 
    ON dbo.sysobjects.uid = dbo.sysusers.uid 
WHERE 
    xtype = 'TR'

01/07/2005, 15h24
krest

salut,

j'ai essayé par script et j'ys suis pas arrivé.
mais en faisant dans EM sur la base de données :
click droit->toutes les taches -> générer un script sql. Ensuite
Appyuer sur afficher tous-> cocher les tables (et les vues si besoins)->aller sur l'onglet 'Mise en forme'->décocher générer la commande drop et create pour chaque objet-> aller sur onglets options->cocher générer un script pour les index et plus si affinité.

J'ai testé avec cette méthode j'ai les script des indexs mais aussi ceux des clés primaires donc à voir si ça te convient.

krest

ps c'est un peu long

Pour info, voici le script :

Code:

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
set transaction isolation level read uncommitted
 
set nocount on
 
select
	'IF EXISTS(SELECT 1 FROM SYSINDEXES WHERE ID = OBJECT_ID('''+tab.name+''') AND NAME = '''+ind.name+''')
	DROP INDEX ['+tab.name+'].['+ind.name+']
'
	+ 'GO' + '
'
	+ 'CREATE'
	+ case when INDEXPROPERTY(ind.id, ind.name, 'IsUnique') = 1 then ' UNIQUE' else '' end
	+ case when INDEXPROPERTY(ind.id, ind.name, 'IsClustered') = 1 then ' CLUSTERED' else ' NONCLUSTERED' end
	+ ' INDEX ['+ind.name+']'
	+ ' ON ['+user_name(tab.uid)+'].['+tab.name+']'
	+ ' ('+
	+ '['+index_col(tab.name, ind.indid, 1)+']'
	+ isnull(', ['+index_col(tab.name, ind.indid, 2)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 3)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 4)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 5)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 6)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 7)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 8)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 9)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 10)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 11)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 12)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 13)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 14)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 15)+']', '')
	+ isnull(', ['+index_col(tab.name, ind.indid, 16)+']', '')
	+ ') WITH FILLFACTOR = ' + convert(varchar, case when ind.OrigFillFactor = 0 then 100 else ind.OrigFillFactor end)
	+ case when INDEXPROPERTY(ind.id, ind.name, 'IsPadIndex') = 1 then ', PAD_INDEX' else '' end
	+ case when ind.status & 1 = 1 then ', IGNORE_DUP_KEY' else '' end
	+ ' ON ['+fg.groupname+']'+'
GO'+'
'
from sysindexes ind
join sysobjects tab on tab.id = ind.id
join sysfilegroups fg on fg.groupid = ind.groupid
where INDEXPROPERTY(tab.id, ind.name, 'IsStatistics') = 0
and tab.type = 'U'
and not exists(
	select 1
	from sysobjects pk
	where pk.type = 'K'
	and pk.parent_obj = tab.id
	and pk.name = ind.name
)
order by tab.name, ind.indid