SQL SERVER – 2008 – List of All Stored Procedure in Database

To get all the stored procedures in Database run the below code in SQL SERVER 2008 or 2005.

use AdventureWorksDW2008R2

GO

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type=‘P’

ALL Stored Procedure

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

8 thoughts on “SQL SERVER – 2008 – List of All Stored Procedure in Database

  1. Very informative post. Its really helpful for me and beginner too. Check out this link too its also having a nice post related to this post over the internet which also explained very well…
    http://mindstick.com/Articles/3db9ca2c-0a41-4f7a-9ce9-82f743563737/?Stored%20Procedure%20in%20Microsoft%20SQL%20Server

  2. Thanks a lot it worked out. This is wat i was looking for to list out only the names of all stored procedures in a particular database.

  3. Hi, I was very useful your query, wanted consult you if you know any way to list the store once, use the result to view permissions apply to a particular user.
    eg, list them with:
    select name, user_name (uid) from sysobjects where type = ‘P’ order by Name

    and give permission to:
    GRANT VIEW DEFINITION ON [dbo]. [Nombre_del_SP] TO [user]

    But somehow I’m thinking of doing more automatic, not to go one by one.
    If you got any ideas I really appreciate it!

    a greeting.
    Guillermo.

  4. i’m getting this error
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘‘’.
    when using this
    use Test

    GO
    SELECT NAME FROM SYS.ALL_OBJECTS WHERE type=‘P’ and is_ms_shipped = 0

    • Yes I got your doubt. I request you to please copy the code and paste in your SSMS but remove the single quote from before and after ‘P’ , and type manually, because when copied it becomes some other character .. please try that once.

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