Query

Hi,
I have a lot SProc. in the database , and I need query search in SProc.

Thanks
samprgAsked:
Who is Participating?
 
jagssiduralaConnect With a Mentor Commented:
try below query, i hope this will helps you

Select       name,
      *
from       sys.objects
Where       Object_Id
In
(
    Select id from syscomments where Text like '%your text here%'
)
0
 
Ephraim WangoyaCommented:
You can search the schema routines

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%your search text%'
AND ROUTINE_TYPE='PROCEDURE'
0
 
samprgAuthor Commented:
It is good, but did not bring  all SProc.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ephraim WangoyaCommented:
You can try the syscomments view but I doubt you will get different results

    SELECT OBJECT_NAME(id)
    FROM syscomments
    WHERE [text] LIKE '%your search text%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    GROUP BY OBJECT_NAME(id)
0
 
Anthony PerkinsCommented:
>>It is good, but did not bring  all SProc. <<
Care to elaborate?  If we do not know the specific problem we cannot give you a good solution.
0
 
samprgAuthor Commented:
ewangoya:
First one is beter,but I tested it gives me 2 SPro. but I have 3 SProc

acperkins:
It is very simple. I need query search in all SProc. in the database

Thanks all of you
0
 
sachinpatil10dCommented:
declare @name nvarchar(100)
set @name = 'C%'
select * from sys.objects where type_desc = 'SQL_STORED_PROCEDURE'
and name like @name
0
 
Anthony PerkinsCommented:
>>It is very simple. I need query search in all SProc. in the database<<
Unfortunately that tells us nothing.

Good luck.
0
 
samprgAuthor Commented:
Awesom
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.