Hi ,
How can we find a particular stored procedure. I need to find a stored procedure which I don't know is in which database. Can somebody please, help with a script.
Thanks
A:
Which database server? With MS SQL Server, you can use sp_help 'procname'.
joeslice
2009-07-08 16:27:49
If the question lacks context about which language, platform or database it is, 99% of the time they mean C#, .net and sql-server. It seems to be an endemic set of virtual blinkers.
skaffman
2009-07-08 16:36:14
A:
If it is Sql Server 2005 you can use
SELECT * FROM Sys.Objects where Name = 'YOUR_NAME_HERE' AND type = 'P'
It will tell you if the procedure is in a particular database.
Kevin
2009-07-08 16:28:10
A:
Which SQL? SQL 2k/2k5/2k8 has management studio which lets you browse. Expand Databases/Database/Programmability/Stored Proceudres
n8wrl
2009-07-08 16:28:39
A:
Replace text to search for with your string and this will search all databases on your server.
exec sp_MSforeachdb 'SELECT db=''?'', [type], [name], [text] FROM [?]..sysobjects a inner join [?]..syscomments b on a.id = b.id where text like ''%Text to search for%'' order by [name], [number]', '?'
u07ch
2009-07-08 16:32:06
+1
A:
One way by using the ANSI information_schema.routines view, change ProcNameHere to the name you want
select * from information_schema.routines
where routine_type = 'PROCEDURE'
and specific_name = 'ProcNameHere'
SQLMenace
2009-07-08 16:32:42
that is the right way to find the procedurs in DB you can find in it's contains tooo for routine_definition
KuldipMCA
2009-07-09 13:20:11