samprg
asked on
Query
Hi,
I have a lot SProc. in the database , and I need query search in SProc.
Thanks
I have a lot SProc. in the database , and I need query search in SProc.
Thanks
ASKER
It is good, but did not bring all SProc.
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)
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%your search text%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
>>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.
Care to elaborate? If we do not know the specific problem we cannot give you a good solution.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
declare @name nvarchar(100)
set @name = 'C%'
select * from sys.objects where type_desc = 'SQL_STORED_PROCEDURE'
and name like @name
set @name = 'C%'
select * from sys.objects where type_desc = 'SQL_STORED_PROCEDURE'
and name like @name
>>It is very simple. I need query search in all SProc. in the database<<
Unfortunately that tells us nothing.
Good luck.
Unfortunately that tells us nothing.
Good luck.
ASKER
Awesom
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINE
WHERE ROUTINE_DEFINITION LIKE '%your search text%'
AND ROUTINE_TYPE='PROCEDURE'