You're doing development, using SQL Server, and the database needs to be restored. Gasp! I have 75 SP's sitting there, half of which I have enhanced in the last 12 hours. Now what? I don't want to lose these stored procedures, older versions of SQL don't have easy ways of saving them, so, lets grab them and put them on my network drive so I have an independant backup. Also, allows for extactraction for easy scripting in other databases.
I had just that nightmare a couple of weeks back. I found the general idea on another site, but the implimentation was poor, so I redid it so it would actually work. This is a VB program that calls stored procedures. One that I created, the other is a system SP.
Using the code
This is so very simple: First is the SQL SP that provides the name and then the VB10 code that leverages it.
ALTER PROCEDURE [dbo].[ReadProcs]
SET NOCOUNT ON;
Name as SPName
ORDER BY Name
This is the first bit of VB10 that kicks off the process:
Private Sub btnSaveProc_Click(sender As System.Object, e As System.EventArgs) Handles btnSaveProc.Click
Dim cData As New cData
Dim dt As New DataTable
Dim iIdx As Int16
Dim cGeneral As New cGeneralFunctions
dt = cData.GetDataTable("ReadProcs", "Y")
For iIdx = 0 To (dt.Rows.Count - 1) Step 1
Finally, the guts. This step gets the name, finds it on the database, and then extracts and saves it to a location on, in this case, my C: drive. After, I can copy to a network location:
Public Function SaveProcs(ByVal SPName As String)
Dim FilePath As String
Dim cmd As New SqlClient.SqlCommand
Dim dat As New SqlClient.SqlDataAdapter
Dim dt As New DataTable
Dim cn As New SqlClient.SqlConnection
FilePath = "C:\SQL Procs\" & SPName & ".sql"
cn = New SqlClient.SqlConnection(cGlobals.ConnectionString)
cmd.Connection = cn
cmd.CommandText = "sp_HelpText"
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandTimeout = 120
cmd.Parameters.Add("@objname", SqlDbType.NVarChar).Value = SPName
Dim sqlrdr As SqlClient.SqlDataReader = cmd.ExecuteReader
Dim wrtr As New System.IO.StreamWriter(FilePath)
Do While sqlrdr.Read
Catch ex As Exception
The trick is using sys.Procedures to get the names of the SP and sp_HelpText to actually GET the proc data. The rest of this reads the proc, and then stores it to a local drive location.
This is also a great way of making copies of complex SP's for your own portfolilo.