How to get a list of user-defined stored procedures

  • Many thanks in advance.

  • SELECT         Name 

    FROM           dbo.SysObjects 

    WHERE          XType 'P' 

                   AND Status >= 

    ORDER BY        Name

  • Another option for you

    SELECT SPECIFIC_NAME 

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_TYPE = 'PROCEDURE'

    ORDER BY SPECIFIC_NAME

     


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • I greatly appreciate your input. INFORMATION_SCHEMA is a view. But could you tell me where I can find it? However, I believe we can only get a list of all stored procedures using your code. In order to get all user-defined stored procedure, we have to use other one.

    Many thanks once again.

  • The view in question is located in the master database.

    However the information displayed comes from the database from where you call the view.

    So if you call the view from pubs, you'll get different results than if you call it from northwind.

     

    If you don't see all the procs using the information_schema views it's because you don't have persmisison on those objects.  My select query skips the owner validation part. 

    Does that clear it up for you?

  • SELECT SPECIFIC_NAME 

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_TYPE = 'FUNCTION'

    ORDER BY SPECIFIC_NAME

    Its the same view, just a different parameter to determine which objects you want to look at.
    In SQL 2000 you can find it in the master database (under views of course).
    In SQL 2005 you can find it in the database you are looking at in views\system views
     

    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • Thank you so much for you guy's inputs. I forgot INFORMATION_SCHEMA is the owner of the view of ROUTINES. However, only Ninja's code could retrieve a list of all user-defined stored procedure. Notice, I do not want to retrieve all of stored procedures!

  • Can you post some sample data and the required output... without that we can't help you anymore than we already did.

  • Your code did work. I mean your code is the only choice we have at this moment.

     

    Many thanks once again.

  • Alright, thanx for clearing this up.

     

    Good luck finishing that project!

  • The best I've designed is below.  The reason I specifically called out name is because these are specific system objects that are created t install an not marked as MS Shipped.  I could have used '%diagram%' but I would have to worry if anyone in the past or future will call a FCN or SP using that word.  Therefore, I called each object specifically so...hopefully the future name won't follow these conventions.  There is a likelihood this will happen so periodically reviewing without the filter is important.

    select *

    from sys.objects

    where is_ms_shipped=0

    and type not in ('D','U','PK','UQ','SN')

    and name not like '%diagram'

    and name not like 'sp_help%'

    and name not like '%diagrams'

    and name not like '%diagramobjects'

    • This reply was modified 1 year ago by  jneal6927.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply