List of Stored Procedures

  • Howdy

    What can be done to generate a list of all stored procedures in a given database or on a specific server?

  • One way is to run

    for Single DB

    select [name] from sysobjects where xtype = 'P' and base_schema_ver != 16 order by [name]

    for All DB

    sp_MSFOrEachDB 'select ''?'' AS DB , [name] from ?..sysobjects where xtype = ''P'' and base_schema_ver != 16 order by [name]'

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

    Edited by - antares686 on 04/08/2002 07:45:06 AM

  • Hi

    That's the trick.

    Thanks a lot.

  • Actually if you use the category != 2 instead of base_schema_ver != 16 you will get all of the user defined stored procedures in a database.

    Single Ex:

    select [name]

    from sysobjects

    where xtype = 'P'

    and category != 2

    order by [name]

    Multiple Ex:

    sp_MSFOrEachDB 'select ''?'' AS DB , [name]

    from ?..sysobjects

    where xtype = ''P''

    and category != 2

    order by [name]'

  • Single DB

    select db_name() as [PROCEDURE_QUALIFIER],

    user_name(o.uid) as [PROCEDURE_OWNER],

    o.name as [PROCEDURE_NAME]

    from sysobjects as o

    whereobjectproperty(o.id, 'IsProcedure') = 1 and

    objectproperty(o.id, 'IsMSShipped') = 0

    order by o.name

    All DB

    execute sp_msforeachdb N'

    select ''?'' as [PROCEDURE_QUALIFIER],

    user_name(o.uid) as [PROCEDURE_OWNER],

    o.name as [PROCEDURE_NAME]

    from ?..sysobjects as o

    whereobjectproperty(o.id, ''IsProcedure'') = 1 and

    objectproperty(o.id, ''IsMSShipped'') = 0

    order by o.name'

Viewing 5 posts - 1 through 4 (of 4 total)

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