Discussion :

[Dschub]

Bonjour,

J'ai sur ma base de données beaucoup de procédures stokées (2500 environ) et je voudrai savoir quelles sont les procédures qui font des inserts, des deletes, des updates.

J'ai pensé à utiliser les tables systèmes comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
select 
	routine_name,
	routine_definition 
from 
	information_schema.routines
where 
	routine_type='PROCEDURE'
 
order by routine_name

ce qui me sort la liste des procédures avec leur définition. Je voudrai éviter de me taper toutes ces procédure a la main et faire directement un contains() sur la définition ma procédure pour avoir un résultat rapide et complètement sûr.

J'ai donc modifié ma requête comme cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
select 
	routine_name,
	routine_definition 
from 
	information_schema.routines
where 
	routine_type='PROCEDURE'
	and contains(routine_definition,' delete ')
order by routine_name

mais j'obtiens l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Msg*7601, Niveau*16, État*2, Ligne*1
Impossible d'utiliser le prédicat CONTAINS ou FREETEXT sur table ou vue indexée 'information_schema.routines', car il n'y a pas d'index de texte intégral.

Est-ce que quelqu'un à une idée pour contourner le problème?

[Dschub]

Voilà plus le résultat que j'aimerai obtenir (tout dans une seule requête):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
select 
	routine_name,
	case when contains(routine_definition,' delete ') then '1' else '0' end as del,
	case when contains(routine_definition,' update ') then '1' else '0' end as up,
	case when contains(routine_definition,' insert ') then '1' else '0' end as ins
from 
	information_schema.routines
where 
	routine_type='PROCEDURE'
order by routine_name

Mais j'ai aussi la même erreur qu'avant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Msg*7601, Niveau*16, État*2, Ligne*2
Impossible d utiliser le prédicat CONTAINS ou FREETEXT 
sur table ou vue indexée information_schema.routines, 
car il n y a pas d index de texte intégral.

[Dschub]

Bon j'ai essayé de passer par syscomments et sysobjects mais ca fait exactement pareil :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
select  
	name, 
	text,
	case when contains(text,' delete ') then '1' else '0' end as del,
	case when contains(text,' update ') then '1' else '0' end as up,
	case when contains(text,' insert ') then '1' else '0' end as ins
from 
	dbo.syscomments, 
	dbo.sysobjects
where 
	syscomments.id = sysobjects.id
	And sysobjects.xtype = 'P'

La même erreur que je commence à avoir en horreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Msg*7601, Niveau*16, État*2, Ligne*1
Impossible d'utiliser le prédicat CONTAINS ou FREETEXT sur table ou vue indexée 'dbo.syscomments', car il n'y a pas d'index de texte intégral.

[Dschub]

Bon ben je viens de trouver une façon de faire en utilisant le mot clé lilke

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
select 
	routine_name,
	routine_definition,
	case when  routine_definition like '%delete%' then '1' else '0' end as del,
	case when  routine_definition like '%update%' then '1' else '0' end as up,
	case when  routine_definition like '%insert%' then '1' else '0' end as ins,
	case when  routine_definition like '%select%' then '1' else '0' end as sel
from 
	information_schema.routines
where 
	routine_type='PROCEDURE'
order by routine_name

Ca fonctionnne sans erreur et me donne un résultat correct dans un temps respectable. J'ai pas encore véritable compris pourquoi l'unmarche et pas l'autre, je vais essayer de faire des recherches dès que j'ai le temps.

[SQLpro]

CONTAINS et FREETEXT ne peuvent être invoqué que si vous avez préalablement mis en place un catalogue d'indexation textuel, ce qui est impossible sur des vues de méta données.

A +

[agemis31]

Bonsoir,

information_schema.routines.routine_definition est un nvarchar(4000),
si vous avez des procédures stockées de plus de 4000 caractères, il est possible que vous ne trouviez pas toutes les références que vous cherchez, puisque seul
le début de votre procédure stockée pourra être stocké dans 4000 caractères.

Sous 2005, 2008. Utilisez plutôt sys.sql_modules.definition qui est de type nvarchar(max)

@+