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 (http://blog.SQLAuthority.com)

About these ads

65 thoughts on “SQL SERVER – 2005 – List All Stored Procedure in Database

  1. 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

  2. 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

  3. 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

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

    Select * from all_objects where object_type=’PROCEDURE”;

  5. 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’)

  6. 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.

  7. @Manoj,

    Select SO.Name [Stored Procedure Name]
    from sysobjects SO
    join Syscomments SC on SO.id = SC.id
    where SO.type = ‘P’
    AND SC.encrypted = 1

    Ex: Create a procedure with encryption and check,

    Create Procedure USP_Ex1
    with encryption
    as
    select * from sysobjects

    go

    Select SO.Name [Stored Procedure Name]
    from sysobjects SO
    join Syscomments SC on SO.id = SC.id
    where SO.type = ‘P’
    AND SC.encrypted = 1

    go

    drop procedure USP_Ex1

    ~ IM.

  8. how to structure a table for view n sir one question i want to apply for dba traning for 6 months bt me heard about no training giving in dba is it true or not plz revert me me doing mca(5 sem)

  9. 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

  10. 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’;

  11. @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.

    • 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.

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

  13. 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’

  14. 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.

  15. 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?

  16. 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’)

  17. 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

  18. 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

  19. 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.

  20. How can I find stored procedures that have never been executed. Not only in the time the log persists in memory? Any idea?
    If is not posible I am trying to find out how to save the number of times the stored procedures of my database are executed. Any help please. It is driving me crazy!! Maybe there is a table where all this information is saved.

  21. List Stored Procedures including plain script:

    SELECT r.ROUTINE_NAME, r.Routine_Definition
    FROM INFORMATION_SCHEMA.Routines r order by r.ROUTINE_NAME

  22. HOW CAN I RESOLVE THIS
    list the titles, prices and advances
    of all the books with the
    price greater than 20% of the advance
    thanks

  23. Hi,

    I need to pull the all userdefined object info from one database.

    User defined objects – Tables,index,constraints ,SP,Functions Triggers….

    i want the output as below

    DBName ObjectName ObjectType …..

    Can anyone help me please

  24. Dear sir,

    I have received one database from client i had made changes like, create,insert,update, delete, in table and stored procedures, could you please help me how to find the things which i had updated by using any query…

  25. Hi Pinal,

    Can you please tell me, how to get the list of user stored procedure that are not executed for long time or unused user stored procedure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s