SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script all Stored procs for all databases on a server


Script all Stored procs for all databases on a server

Author
Message
ramadesai108
ramadesai108
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5199 Visits: 690
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.
ramadesai108
ramadesai108
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5199 Visits: 690
I found it myself and it is:


EXECUTE sp_msforeachdb 'select specific_catalog, specific_name, routine_definition from information_schema.routines'
ramadesai108
ramadesai108
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5199 Visits: 690
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'
Lowell
Lowell
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155493 Visits: 41543
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
ramadesai108
ramadesai108
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5199 Visits: 690
but that does not give me database name.
Adiga
Adiga
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12958 Visits: 21012
Add db_name() in the select list of the query that Lowell had given.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
mister.magoo
mister.magoo
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22456 Visits: 7931
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • mister.magoo
    mister.magoo
    SSC-Insane
    SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

    Group: General Forum Members
    Points: 22456 Visits: 7931
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Lowell
    Lowell
    SSC Guru
    SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

    Group: General Forum Members
    Points: 155493 Visits: 41543
    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
    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
    ramadesai108
    ramadesai108
    SSCertifiable
    SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

    Group: General Forum Members
    Points: 5199 Visits: 690
    That worked, Thanks.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum







































































































































































    SQLServerCentral


    Search