SQL SERVER – 2005 – List All Stored Procedure in Database

Run following simple script on SQL Server 2005 to retrieve all stored procedure in database.

SELECT
*
FROM sys.procedures;

This will ONLY work with SQL Server 2005.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Rules of Third Normal Form and Normalization Advantage – 3NF
Next Post
SQL SERVER – Correct Syntax for Stored Procedure SP

Related Posts

66 Comments. Leave new

  • Hi,

    How to extract an encrypted stored procedure to DDL or other binary file? And this file can be used to run on SQL Query or other tools to copy procedures only to other database without moving other objects.

    Thanks,
    Xiaogang

    Reply
  • Hi,

    Try this on SQL Prompt, u can view all the Procedures

    SELECT OBJECT_NAME, OBJECT_TYPE FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE=’PROCEDURE’;

    Reply
  • PROGRAMMATICALLY – how do you list ALL of the parameters for a given stored procedure?

    Reply
  • Imran Mohammed
    April 20, 2010 8:24 am

    @Rob

    For A quick look,

    use mydb
    Exec Sp_help [ownername.spname]

    Above script gives you details of procedure including the parameters information for that stored procedure.

    ~ IM.

    Reply
    • Hi Imran Mohammed,
      Can you tell me how to list ALL of the parameters for ALL stored procedures simultaneously in a single query ?? It would make learning stored procedures of SQL Server 2005 very easy. Is there any keyboard shortcut for this ??

      Thanks & Regards,
      Pratik.

      Reply
  • Please use :

    SELECT *
    FROM sys.all_objects where type=’p’ order by name

    Reply
  • Thanks it was helpful

    Reply
  • How to get list of input parameters of any stored proc which listed by querying sys.objects or dbo.sysobjects in 2005 ???

    Reply
  • Guys we got da solution for same:
    is like below:

    SELECT Name,PARAMETER_NAME,DATA_TYPE,PARAMETER_MODE,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.PARAMETERS A
    INNER JOIN
    Sys.objects B ON
    A.SPECIFIC_Name = B.Name
    WHERE B.Name LIKE ‘SprocName%’ AND TYPE =’P’

    Reply
  • hi friend,
    i posted the following question in ur all forum,

    i have a bulk XML database, i want this database to be import in sql server 2005 table using CREATE PROCEDURE, pls help out this soluition.

    Reply
  • Can anyone please tell me, how do I ge the listing of the all the Stored Procs which are updating records or deleting records in/of a give table?

    Reply
  • This is very old style.
    Please give some thing new code

    Reply
  • try this:

    select o.name as sp_name,
    (len(c.text) – len(replace(c.text, char(10), ”))) as lines_of_code,
    case when o.xtype = ‘P’ then ‘Stored Procedure’
    when o.xtype in (‘FN’, ‘IF’, ‘TF’) then ‘Function’
    end as type_desc
    from sysobjects o
    inner join syscomments c
    on c.id = o.id
    where o.xtype in (‘P’, ‘FN’, ‘IF’, ‘TF’)
    and o.category = 0
    and o.name not in (‘fn_diagramobjects’, ‘sp_alterdiagram’, ‘sp_creatediagram’, ‘sp_dropdiagram’, ‘sp_helpdiagramdefinition’, ‘sp_helpdiagrams’, ‘sp_renamediagram’, ‘sp_upgraddiagrams’, ‘sysdiagrams’)

    Reply
  • Hi,

    I need to get the stored procedures from two different database. Is that possible? If so, help me.

    I was using this query to retrieve stored procedures from the SQL Server. But it retrieves ONLY from the currently mounted database.

    select specific_name,specific_catalog,created, last_altered from information_schema.routines
    where routine_type=’PROCEDURE’
    order by created, last_altered

    What I need is,

    (i.e) Consider Database1 and Database2

    specific_namespecific_catalog,created, last_altered

    Thanks,
    Sabarish

    Reply
  • Hi,

    I need to get the stored procedures from two different database. Is that possible? If so, help me.

    I was using this query to retrieve stored procedures from the SQL Server. But it retrieves ONLY from the currently mounted database.

    select specific_name,specific_catalog,created, last_altered from information_schema.routines
    where routine_type=’PROCEDURE’
    order by created, last_altered

    What I need is,

    (i.e) Consider Database1 and Database2

    =========================================
    specific_name specific_catalog created last_altered
    =========================================
    Sp_getname Database1 2009-01-09 2009-01-09
    Sp_getage Database2 2009-01-09 2009-01-09
    =========================================

    Thanks,
    Sabarish

    Reply
  • Would it possible to create a query that would extract the code of each stored procedure into distinct files?

    Reply
  • The above Query is not working in list the all procedures in Database

    Reply
  • Simple and exec sp_stored_procedures

    Reply
  • Hi Pinal / All,

    is there any way to restore the deleted stored procedure in SQL 2005 ?

    Thanks,
    Yash

    Reply
  • I want to display all the system stored procedures name with input/output parameters name along with their datatypes.

    Select * from sys.all_objects where type=’p’ doesn’t give information about input/output parameter names.

    exec sp_stored_procedures displays information about i/o parameters name but what is PROCEDURE_TYPE.

    What is the name of main tab;e and where can I find that..

    Same thing I want to know for System Functions.

    Reply
  • select parameter_name from information_schema.parameters where specific_name = ‘SP Name’

    Reply

Leave a Reply

Menu