CODEWITHSUNDEEP
sqlstored-procedures
user70636
user70636

Reputation: 1089

Finding a stored procedure

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

Upvotes: 1

Views: 193

Answers (5)

SQLMenace
SQLMenace

Reputation: 135151

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'

Upvotes: 1

u07ch
u07ch

Reputation: 13702

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]', '?'

Upvotes: 0

n8wrl
n8wrl

Reputation: 19765

Which SQL? SQL 2k/2k5/2k8 has management studio which lets you browse. Expand Databases/Database/Programmability/Stored Proceudres

Upvotes: 0

kemiller2002
kemiller2002

Reputation: 115538

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.

Upvotes: 0

joeslice
joeslice

Reputation: 3464

Which database server? With MS SQL Server, you can use sp_help 'procname'.

Upvotes: 0

Related Questions