List of stored procedures

Hello,
What table can you use to get a list of all of the stored procedures in a
database? I need to be able to delete the ones created by users. I am
assuming that I will need to use a cursor to step through and delete them.
I created one for tables as an example (see below).
--
Thanks in advance,
sck10
USE MyDatabase
DECLARE @.strCursor varchar(255), @.strExe varchar(255)
-- Declare Cursor Batch
DECLARE csrPKey CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE 'tbl%') OR (TABLE_NAME LIKE 'tlkp%')
ORDER BY TABLE_NAME
OPEN csrPKey
FETCH NEXT FROM csrPKey INTO @.strCursor
WHILE @.@.FETCH_STATUS = 0 BEGIN -- 0 = success, -1 = outside recordset, -2
= row no longer exist
SELECT @.strCursor
SET @.strExe = 'DROP TABLE ' + @.strCursor
EXECUTE (@.strExe)
FETCH NEXT FROM csrPKey INTO @.strCursor
END
CLOSE csrPKey
DEALLOCATE csrPKeyYou can do a similar thing with the INFORMATION_SCHEMA.ROUTINES view
(this would be the Microsoft approved, kosher way) or the dbo.sysobjects
table in the database (which is how I'd probably do it - old habits die
hard, but I'm trying to be good and start using the new catalog views &
DMVs).
*mike hodgson*
http://sqlnerd.blogspot.com
sck10 wrote:

>Hello,
>What table can you use to get a list of all of the stored procedures in a
>database? I need to be able to delete the ones created by users. I am
>assuming that I will need to use a cursor to step through and delete them.
>I created one for tables as an example (see below).
>|||Hello Sck10,
As for the database server, is it a SQL Server 2000 instance or SQL Server
2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
catalog view to query all the user defined store procedures in a certain
database.
#sys.procedures (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188737.aspx
e.g. select * from sys.procedures
For sqlserver 2000, there is no such system catalog view, however, we can
query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
select * from sysobjects where type='P' order by name asc
This will return all the storeprocedures available in the current database
context(included system sp), you can filtering through the "category" field
in this system table to query user defined sp only.
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I'm guessing if the OP is using the INFORMATION_SCHEMA views rather than
the sys.* views then he's probably working with SQL 2000. As for SQL
2000, what's wrong with the INFORMATION_SCHEMA.ROUTINES view? I thought
Microsoft advised people to use the INFORMATION_SCHEMA views rather than
the system tables such as dbo.sysobjects.
*mike hodgson*
http://sqlnerd.blogspot.com
Steven Cheng[MSFT] wrote:

>Hello Sck10,
>As for the database server, is it a SQL Server 2000 instance or SQL Server
>2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
>catalog view to query all the user defined store procedures in a certain
>database.
>#sys.procedures (Transact-SQL)
>http://msdn2.microsoft.com/en-us/library/ms188737.aspx
>e.g. select * from sys.procedures
>For sqlserver 2000, there is no such system catalog view, however, we can
>query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
>select * from sysobjects where type='P' order by name asc
>This will return all the storeprocedures available in the current database
>context(included system sp), you can filtering through the "category" field
>in this system table to query user defined sp only.
>Hope this helps.
>Regards,
>Steven Cheng
>Microsoft Online Community Support
>
> ========================================
==========
>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
> ========================================
==========
>
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>|||Thanks for your suggestion Mike,
I agree that the INFORMATION_SCHEMA.ROUTINES is also a good approach.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)