locked
Where can I find ... RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Where can I find a list of all the stored proceedures and what they do?
    Wednesday, August 18, 2010 3:21 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Select * from information_schema.routines where ROUTINE_TYPE like 'procedure'


    select b.definition,a.* from sys.all_objects a
inner join sys.all_sql_modules b on a.object_id = b.object_id
and a.type = 'p'

    This second query will list all procedures with it's defination. Hope this helps.

    -Chintak
    • Proposed as answer by Naomi N Friday, August 20, 2010 2:14 AM
    • Marked as answer by Tom Li - MSFT Thursday, August 26, 2010 11:47 AM
    Wednesday, August 18, 2010 4:17 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Select * from information_schema.routines where ROUTINE_TYPE like 'procedure'


    select b.definition,a.* from sys.all_objects a
inner join sys.all_sql_modules b on a.object_id = b.object_id
and a.type = 'p'

    This second query will list all procedures with it's defination. Hope this helps.

    -Chintak
    • Proposed as answer by Naomi N Friday, August 20, 2010 2:14 AM
    • Marked as answer by Tom Li - MSFT Thursday, August 26, 2010 11:47 AM
    Wednesday, August 18, 2010 4:17 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Alternative to Chintak solutions you can use the view sys.procedures.

    SELECT *

    FROM sys.procedures

     

    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Proposed as answer by Naomi N Friday, August 20, 2010 2:14 AM
    Wednesday, August 18, 2010 5:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    To find a word order within a list of SP i have been using the below script

    SELECT

     

    QUOTENAME(USER_NAME(o.schema_id)) +

     

    '.' +

     

    QUOTENAME(o.name) AS ProcedureName

     

    FROM sys.objects o

     

    JOIN sys.sql_modules sm ON

    sm

    .object_id = o.object_id

     

    WHERE

    o

    .type = 'P' AND

    sm

    .definition LIKE '%order%'

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, August 18, 2010 7:01 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote
    All the above replies will give you the objects from SQL server when querying them . You can also use this link http://msdn.microsoft.com/en-us/library/ms187961.aspx   to see what are the system SPs available and their description/usage in all versions of SQL server and they are categorized based on their description.
    Thanks, Leks
    Wednesday, August 18, 2010 8:33 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote 
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
    Will also give you definition of the SP.
    Thursday, August 19, 2010 6:48 AM