Save ALL SPs as files 1

[LNBruno]

I know how to do this the hard way; looking for something a bit less labor-intensive (SQL Server 2000).

I can get a list of all stored procedures on the current database like this:

      SELECT Routine_Name
      FROM Information_Schema.Routines
      WHERE (Routine_Type = 'Procedure' 
         AND Routine_Name NOT LIKE 'dt[_]%'
      ORDER BY Routine_Name

And then change the default destination for Query Analyzer results to "file" (Tools --> Options... --> then select Query Results - SQL Server - General; then select 'Results to file' from the dropdown and set the path for the output).

And run

sp_helptext ProcedureName

ONE PROC AT A TIME to have it saved off as a .rpt file, but is there a way to execute this for all procs using the proc name as the filename?

Reason for this insanity? Version comparision between code in database and that stored in an external source control.



< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLDenis]

Here is where enterprise manager will save you
select all the stored procs you want to script
rightclick-->all tasks-->generate sql script
in the options tab select create 1 file per object


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

If only...

In their infinite wisdom, the powers that be have seen fit to restrict my access to Query Analyzer...

< M!ke >
I am not a hamster and life is not a wheel.

 

[Crowley16]

both query analyser and enterprise manager?

--------------------
Procrastinate Now!

 

[bborissov]

[COLOR=blue]declare[/color] @test [COLOR=blue]varchar[/color](200)

[COLOR=blue]SELECT[/color] @test = [COLOR=#FF00FF]MIN[/color](Routine_Name)
      [COLOR=blue]FROM[/color] Information_Schema.Routines
      [COLOR=blue]WHERE[/color] (Routine_Type = [COLOR=red]'Procedure'[/color]
         AND Routine_Name NOT LIKE [COLOR=red]'dt[_]%'[/color])

[COLOR=blue]declare[/color] @lcSQL [COLOR=blue]varchar[/color](8000)
[COLOR=blue]WHILE[/color] @Test [COLOR=blue]IS[/color] NOT NULL
      [COLOR=blue]BEGIN[/color] 
         [COLOR=blue]SET[/color] @lcSQL = [COLOR=red]'sp_helptext '[/color]+@Test
         [COLOR=blue]EXEC[/color] (@lcSQL)
         [COLOR=blue]SELECT[/color] @test = [COLOR=#FF00FF]MIN[/color](Routine_Name)
		        [COLOR=blue]FROM[/color] Information_Schema.Routines
			    [COLOR=blue]WHERE[/color] (Routine_Type = [COLOR=red]'Procedure'[/color]
				       AND Routine_Name NOT LIKE [COLOR=red]'dt[_]%'[/color]
                       AND Routine_Name > @Test)
      [COLOR=blue]END[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LNBruno]

Ok, I keep calling it QA, but I really am using Management Studio...

< M!ke >
I am not a hamster and life is not a wheel.

 

[LNBruno]

Dynamic SQL! DOH!

Much thanks, Borislav!

< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLDenis]

How is that script going to save every proc to a different file? Isn't that what you asked for?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Nope, it save all in one file. But I think to split file after that is the easier part

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LNBruno]

Just saw that, Denis. Yes, I do need each one in a separate file to make use of the tool I use for comparison....

Always on Fridays!!! ;-)

< M!ke >
I am not a hamster and life is not a wheel.