Listing SPs

Is there anyway of listing all the store procedure in a DB? I need to locate
a SP but I don′t know in which DB is, all I have is the name.
Thanks to everyone,
C.Cesar,
the easiest way is to use the object search utility in query analyser - it
will allow searching in all databases.
HTH,
Paul Ibison|||Is there a query I can run to do that?|||sysobjects table in each db contains the names of the stored procudure
select * from sysobjects where type = 'p' and name = @.procname
Write a cursor to loop thru each db in your server.
HTH, Amol.
"Cesar" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDA4F21-5A2C-4172-AEB5-B65FF859BE0A@.microsoft.com...
> Is there a query I can run to do that?|||Cesar,
(In SQL2000...)
To search a user table named 'Mytable' in all databases...
exec master.dbo.sp_MSobjsearch @.searchkey = N'Mytable', @.objecttype = 1,
@.dbname = [*], @.casesensitive = 0, @.hitlimit = 100, @.status = 1,
@.extpropname = NULL, @.extpropvalue = NULL
Do note that sp_MSobjsearch is a undocumented call and hence the usual
warnings apply.To find out details about the various parameters, do:
USE master
sp_helptext sp_MSobjsearch
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Cesar" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDA4F21-5A2C-4172-AEB5-B65FF859BE0A@.microsoft.com...
> Is there a query I can run to do that?|||Cesar,
To search for a stored procedure named 'MyStorProc' across all databases..
sp_msforeachdb "PRINT '?' select * from [?]..sysobjects where type='P' a
nd
name='MyStorProc'"
Please note that sp_msforeachdb is a undocumented call and hence the usual
warnings apply.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Cesar" <anonymous@.discussions.microsoft.com> wrote in message
news:1C4A03A2-5497-420B-B914-2021A860E065@.microsoft.com...
> Is there anyway of listing all the store procedure in a DB? I need to
locate a SP but I dont know in which DB is, all I have is the name.
> Thanks to everyone,
> C.|||Cesar,
you could use the method that the GUI uses:
exec master.dbo.sp_MSobjsearch @.searchkey = N'storedprocedurename',
@.objecttype = 24, @.dbname = [*], @.casesensitive = 0, @.hitlimit = 100,
@.status = 1, @.extpropname = NULL, @.extpropvalue = NULL
Or you could use the code I have created below. If you want the search
explicitly, then you just need to add the parameter to athe where clause of
the exec statement, eg exec ('select o.name from ' + @.dbname +
'.dbo.sysobjects o where o.xtype = ''P'' and o.name =
''storedprocedurename'' ')
HTH,
Paul Ibison
declare @.dbname varchar(100)
DECLARE databasecursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
OPEN databasecursor
FETCH NEXT FROM databasecursor into @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.dbname as 'Database'
exec ('select o.name from ' + @.dbname + '.dbo.sysobjects o where o.xtype
= ''P'' ')
FETCH NEXT FROM databasecursor into @.dbname
END
CLOSE databasecursor
DEALLOCATE databasecursor
GO|||Thanks to everyone (Paul, Amol and Dinesh)!!!, I′ll try the solutions ASAP
.|||>>I need to locate a SP but I dont know in which DB is, all I have is the
name.
given the name of the SP you can press F4 in Query Analyzer and search.
"Cesar" <anonymous@.discussions.microsoft.com> wrote in message
news:1C4A03A2-5497-420B-B914-2021A860E065@.microsoft.com...
> Is there anyway of listing all the store procedure in a DB? I need to
locate a SP but I dont know in which DB is, all I have is the name.
> Thanks to everyone,
> C.|||Hi,
You can also use the system stored procedure sp_store_procedures
eg:
sp_store_procedures '%cust%'
This returns all the stored procedure name with cust in the procedure name.
Note:
You have to execute the procedure in each of the databases, because this
procedure will search only the current database.
Thanks
Hari
MCDBA
"Cesar" <anonymous@.discussions.microsoft.com> wrote in message
news:1C4A03A2-5497-420B-B914-2021A860E065@.microsoft.com...
> Is there anyway of listing all the store procedure in a DB? I need to
locate a SP but I dont know in which DB is, all I have is the name.
> Thanks to everyone,
> C.