Script all Stored procs for all databases on a server

Question

Post reply

Script all Stored procs for all databases on a server

ramadesai108

SSChampion

Points: 10609
More actions
December 9, 2010 at 11:33 am

#157351

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.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

ramadesai108 SSChampion Points: 10609 More actions · Answer 1

I found it myself and it is:

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

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

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 SSC Guru Points: 323518 More actions · Answer 3

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 SSChampion Points: 10609 More actions · Answer 4

ramadesai108

SSChampion

Points: 10609

December 9, 2010 at 12:35 pm

#1260741

but that does not give me database name.

Adiga One Orange Chip Points: 27284 More actions · Answer 5

Add db_name() in the select list of the query that Lowell had given.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

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);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

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);

Lowell SSC Guru Points: 323518 More actions · Answer 8

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 SSChampion Points: 10609 More actions · Answer 9

ramadesai108

SSChampion

Points: 10609

December 9, 2010 at 1:56 pm

#1260788

That worked, Thanks.