Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script all Stored procs for all databases on a server Expand / Collapse
Author
Message
Posted Thursday, December 09, 2010 11:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
I run the following script to script all stored procs for all databases on a server, but I would also like to display the database names for these stored procs. How do I do that?

EXECUTE sp_msforeachdb 'select s.*, p.*
from ?.sys.sql_modules s
inner join ?.sys.procedures p
on s.object_id = p.object_id'

Thnx.
Post #1032625
Posted Thursday, December 09, 2010 11:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
I found it myself and it is:


EXECUTE sp_msforeachdb 'select specific_catalog, specific_name, routine_definition from information_schema.routines'
Post #1032645
Posted Thursday, December 09, 2010 12:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Made a mistake. The last one did not work. What works is the following, but I also need database name with it. ANY IDEAS???

EXECUTE sp_msforeachdb 'select s.*, p.*
from ?.sys.sql_modules s
inner join ?.sys.procedures p
on s.object_id = p.object_id'
Post #1032651
Posted Thursday, December 09, 2010 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
the OBJECT_NAME function has a second , optional parameter for the db_id for exactly this reason;
this works in 2005 and above;
sp_msforeachdb '
select
object_name(s.object_id,db_id(''?'')),
s.*, p.*
from ?.sys.sql_modules s
inner join ?.sys.procedures p
on s.object_id = p.object_id'

the schema_name() function does not have the optional parameter in 2005, so if you need the schema (assuming you have otehrs than dbo) that might take some more work.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1032658
Posted Thursday, December 09, 2010 12:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
but that does not give me database name.
Post #1032670
Posted Thursday, December 09, 2010 12:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 30, 2014 8:52 AM
Points: 1,618, Visits: 20,966
Add db_name() in the select list of the query that Lowell had given.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1032680
Posted Thursday, December 09, 2010 12:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 1,651, Visits: 5,202
ramadesai108 (12/9/2010)
I found it myself and it is:


EXECUTE sp_msforeachdb 'select specific_catalog, specific_name, routine_definition from information_schema.routines'


It does work, you just missed the "?"
EXECUTE sp_msforeachdb 'select specific_catalog, specific_name, routine_definition from ?.information_schema.routines'



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1032682
    Posted Thursday, December 09, 2010 12:52 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:20 PM
    Points: 1,651, Visits: 5,202
    And just to show how it can be done with the first query:

    EXECUTE sp_msforeachdb 'use ?;select DB_NAME(),s.*, p.*
    from sys.sql_modules s
    inner join sys.procedures p
    on s.object_id = p.object_id'




    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1032683
    Posted Thursday, December 09, 2010 1:41 PM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Yesterday @ 2:26 PM
    Points: 12,744, Visits: 31,071
    ramadesai108 (12/9/2010)
    but that does not give me database name.


    you forgot how the proc works! the question mark is substituted with the dbname!
    db_name() function will keep returning master or whatever db you started the cursor in.
    sp_msforeachdb '
    select
    ''?'' AS DBName,
    object_name(s.object_id,db_id(''?'')),
    s.*, p.*
    from ?.sys.sql_modules s
    inner join ?.sys.procedures p
    on s.object_id = p.object_id'




    Lowell

    --There is no spoon, and there's no default ORDER BY in sql server either.
    Actually, Common Sense is so rare, it should be considered a Superpower. --my son
    Post #1032713
    Posted Thursday, December 09, 2010 1:56 PM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Tuesday, January 28, 2014 3:52 PM
    Points: 377, Visits: 685
    That worked, Thanks.
    Post #1032725
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse