how to save all Stored Procedures to files using a script

[DougP]

Is there a way to create a SQL script to read all the SP' in a Server that start with sp_. Then save them each to there own filename called the name of the SP. like sp_GetID.txt


DougP

 

[djj55]

Many are compiled, thus no code. Also the line breaks need to be worked with.

Look at sys.sql_modules, sysobjects and sys.schemas.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[DougP]

this lists all the SP's. Is there a way to open each one?
If I right click, on an SP and click Modify I can see it on the screen, then save it as a Script xxxx.sql
is there a way to do something like that?

. 		SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], 
		T.NAME + '|' +C.NAME AS TableAndColumn , 
		P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],  
		CAST(P.PRECISION AS VARCHAR) +'/' + CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
	INTO SOWTimeReportingTEST.dbo.AllTablesPROD 
	FROM SOWTimeReporting.SYS.OBJECTS AS T
		JOIN SOWTimeReporting.SYS.COLUMNS AS C
			ON T.OBJECT_ID=C.OBJECT_ID
		JOIN SOWTimeReporting.SYS.TYPES AS P
			ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
	WHERE T.TYPE_DESC='USER_TABLE'
	Order By[TABLE NAME],[COLUMN NAME];

DougP

 

[gmmastros]

Add a column to the select list

Object_definition(t.object_id)

This will return the code of the stored procedure for you. Saving each one to a separate file will be a little more tricky.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

does SQL have some kind of Stream reader/writer to write the results of the above script ie.e Object_definition(t.object_id)
as gmmastros mentioned to a file name?

DougP

 

[philhege]

Doug, you can write an SSIS package or use BCP to send the SP text to a file.

-----------
With business clients like mine, you'd be better off herding cats.