Journey to SQL Authority with Pinal Dave

SELECT *
    FROM sys.procedures;

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 13 Comments

13 Responses to “SQL SERVER - 2005 - List All Stored Procedure in Database”

1. on March 14, 2008 at 5:18 pm Preethi

   Hello

   how to find the rate of interest - using stored procedure concept in sql server.

   
   
2. on March 22, 2008 at 5:49 pm SAVITHA

   how can i get only user defined procedure…

   
   
3. on April 1, 2008 at 1:39 pm AgnesLoyola

   Hi Savitha

   You can use use the below query to list all User Defined Stored procedures in a database:

   Select * from sys.procedures where [type] = ‘P’ and is_ms_shipped = 0 and [name] not like ’sp[_]%diagram%’

   OR

   Select * from sys.objects where type=’p’ and is_ms_shipped=0 and [name] not like ’sp[_]%diagram%’

   ‘NOT LIKE’ part is added to get rid of stored procedures created during database installation.

   i have taken this from http://vadivel.blogspot.com/2006/10/listing-dropping-all-stored-procedures.html

   
   
4. on April 2, 2008 at 2:09 pm Sumon

   We can find the Stored Procedures of a data base like this:

   select * from dbo.sysobjects where xtype=’p’ and OBJECTPROPERTY(id, N’IsProcedure’) = 1

   
   
5. on June 17, 2008 at 12:25 pm tushar

   Is it possible to insert data using Stored procedure in number of tables with different fields number of fields

   
   
6. on June 20, 2008 at 8:45 am Amol Kagde

   hi tushar,

   Yes this is possible using one stored procedure , that insert different data fields in diffrent tables.

   Create Proc name
   (
   Declare here all fields
   )
   as
   Write here different insert query statement for differrent tables simultaneously it will fire and exe.

   ok bye

   
   
7. on July 8, 2008 at 10:26 pm eruntale

   how do I do this for SQL server 2000?

   
   
8. on August 30, 2008 at 3:59 pm Hanuman

   We can get all procedures in the follwoing way .
   I tried its working …

   Select * from all_objects where object_type=’PROCEDURE”;

   
   
9. on September 3, 2008 at 10:42 am Ben GU

   in sql 2005

   SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published,
   is_schema_published
   FROM sys.all_objects
   WHERE (is_ms_shipped = 0) AND (type_desc = N’SQL_STORED_PROCEDURE’)

   
   
10. on September 10, 2008 at 11:46 am Amit

    Can anyone tell me where stored procedure is stored in SQL Server 2005?

    
    
11. on September 12, 2008 at 3:46 am Jack

    you guys are all crazy:
    (from SQL Sever 2005 Management Studio)

    SELECT * FROM sys.procedures
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ’sys.procedures’.

    Select * from all_objects where object_type=’PROCEDURE’
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘all_objects’.

    Probably because it’s a SQL Server 2000 database.

    
    
12. on September 18, 2008 at 1:47 am atul

    Hi How we can view the stored procedure in query anlyzer
    which was written previousely.
    what is the sytax to review that procedure.

    
    
13. on September 18, 2008 at 9:27 pm Carol

    Is there a way to do this using SS2k?