Could anybody please tell mehow I can search for a stored procedurein SQL Server 2005? Iknow the name of the stored procedure and Iwant to find in which database that stored proc is located/stored and Iwant to see the code of it. (I have all the necessaary previleges.) Please tell me how I can I do this.
Thanks in advance.
Regards,
Ram.
Hi,
you can check for existance of stored procedure as ..
declare @name varchar (100)
declare @spname varchar(100)
declare cu1 cursor for
select name from master..sysdatabases
open cu1
fetch next from cu1 into @name
while @@fetch_status =0
begin
exec ('select * from ' + @name + '..sysobjects where xtype = ''P'' and name = ''' + @spname +'''')
if @@rowcount> 0
begin
select @name as DBName
end
fetch next from cu1 into @name
end
close cu1
deallocate cu1
will give you the database name where stored proceure exists and then u can use following statement to get the contents of sp
use dbanme;
sp_helptext sp_name
hi ram,
You can open the Sql server management studio
connect to your sql server and then
expand the database.
you can find the stored procedure in the programmability tabs of the
database
regards,
joey
You can also try the following
EXEC
sp_MSforeachdb @command1="USE ?;select SPECIFIC_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME LIKE '%MyStoredPrcedure%'"Shallu,
Thank you very much for taking time to explain this to me. It was very helpful to me.
With best regards,
-Ram.
Hi Joey,
Thank you very much for explaining this to me. Thanks for your time.
With best regards,
-Ram.
Hi Neeraj,
Thank you very much for explaining this to me. Thanks for your time.
With best regards,
-Ram.