CODEWITHSUNDEEP
sql-server
eXavier
eXavier

Reputation: 4981

How to automate changes in stored procedures in SQL Server?

I've got two databases in SQL Server where one database (ConfigDb) references other (DataDb). Many of stored procedures and functions in ConfigDb uses tables and functions/SPs in DataDb. They are referenced like [DataDb].[dbo].[the_object].

Now, I would need to clone the databases to test version, i.e. restore them from backups as ConfigDb_Test and DataDb_Test. Obviously, ConfigDb_Test references DataDb, not the DataDb_Test.

Any tips how to handle this better than opening SP by SP and edit manually?

EDIT

For refernce, I put the utility at GitHub

Upvotes: 2

Views: 202

Answers (1)

Dave C
Dave C

Reputation: 7392

OK, it is in VB (not c#) -- and I'm not a .net guy, so I'm sure there's a better way to do this.

I caution you to step through and observe the code work, but I use this a lot, and it works great!

Once you enter your servername/credentials, a list of db's will appear (system db's are excluded). Select the db's you want to run against (check list) and enter the find/replace text strings. When you click start, each proc will be opened/scanned, and a REPLACE() is run on matching strings -- so be careful what you put in the FIND/REPLACE text boxes!

Here it goes...

Please, be careful with this, and use at your own risk.

form2.vb

form2.vb

Object Names (in order)

  • cmbServer (Combo Box)
  • chkSSPI (Check Box)
  • txtUser (Text Box)
  • txtPass (Text Box)
  • btnConnect (Button)
  • btnExit (Button)

Code Behind:

Imports System.Data.SqlClient

Public Class Form2
    Public objConn As SqlConnection = New SqlConnection()

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cmbServer.SelectedIndex = 0
        chkSSPI.Checked = True
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Application.Exit()
    End Sub

    Private Sub chkSSPI_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkSSPI.CheckedChanged
        txtUser.Enabled = Not chkSSPI.Checked
        txtPass.Enabled = Not chkSSPI.Checked
        txtUser.Select()
    End Sub

    Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
        If chkSSPI.Checked = False Then
            objConn.ConnectionString = String.Format("Data Source={0};Initial Catalog=master;User ID={1};Password={2};", cmbServer.Text, txtUser.Text, txtPass.Text)
        Else
            objConn.ConnectionString = String.Format("Data Source={0}; Initial Catalog=master; Integrated Security=SSPI", cmbServer.Text)
        End If
        Dim frm2 As Form = New frmDBList()
        Try
           objConn.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Hide()
        frm2.Show()
    End Sub

    Private Sub txtUser_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtUser.TextChanged

    End Sub
End Class

frmDBList.vb

frmDBList.vb

Object Names (in order):

  • clbDatabase (Checked List Box)
  • txtFind (Text Box)
  • txtReplace (Text Box)
  • lblDBName (Label)
  • lblProcNum (Label) [0]
  • lblProcCount (Label) [123]
  • lblProcName (Label)
  • btnCheckAll (Button)
  • btnUnCheckAll (Button)
  • btnStart (Button)
  • btnClose (Button)

Code Behind:

Imports System.Data.SqlClient
Imports System.IO

Public Class frmDBList
    Dim procText As String
    Dim procList As New ArrayList
    Dim errorLog As New ArrayList
    Dim dbcount As Int16
    Dim proccount As Int16
    Dim replacecount As Int16
    Dim procupdate As Boolean

    Public Sub LogError(ByVal text As String, ByVal dbname As String, ByVal procname As String)
        errorLog.Add("db=" + dbname + " proc=" + procname + " error=" + text)
    End Sub

    Public Sub SaveLog()
        Dim datetime As String = Now.ToString()
        datetime = Replace(datetime, "/", "")
        datetime = Replace(datetime, ":", "")
        Dim filename As String = "c:\procchanger_errorlog " + datetime + ".txt"
        Dim objWriter As New System.IO.StreamWriter(filename)
        For c As Int16 = 0 To errorLog.Count - 1
            objWriter.WriteLine(errorLog(c).ToString())
        Next
        objWriter.Close()
    End Sub

    Public Sub AddListBoxItem(ByVal Item As Object, ByVal Check As Boolean)
        clbDatabase.Items.Add(Item, Check)
    End Sub

    Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click
        btnStart.Enabled = False
        btnClose.Enabled = False
        errorLog.Clear()
        dbcount = 0
        proccount = 0
        replacecount = 0
        grpProgress.Visible = True
        If clbDatabase.SelectedItems.Count = 0 Then
            MsgBox("Please select at least one database to process.", vbOKOnly)
        Else
            For i As Integer = 0 To clbDatabase.Items.Count - 1
                If clbDatabase.GetItemChecked(i) = True Then
                    lblDBName.Text = clbDatabase.Items(i).ToString()
                    dbcount += 1
                    procList.Clear()
                    GetProcList(clbDatabase.Items(i).ToString())
                End If
            Next
            MsgBox("Complete.  Replaced " + replacecount.ToString() + " occurrences, in " + proccount.ToString() + " stored procedures, across " + dbcount.ToString() + " databases.")
            If errorLog.Count > 0 Then
                SaveLog()
            End If

            grpProgress.Visible = False
            For i As Integer = 0 To clbDatabase.Items.Count - 1
                clbDatabase.SetItemChecked(i, CheckState.Unchecked)
            Next
        End If
        If Form2.objConn.State = ConnectionState.Open Then Form2.objConn.Close()
        btnStart.Enabled = True
        btnClose.Enabled = True
    End Sub

    Public Sub GetProcList(ByVal dbname As String)
        If Form2.objConn.State = ConnectionState.Closed Then
            If Form2.chkSSPI.Checked = False Then
                Form2.objConn.ConnectionString = String.Format("Data Source={0};Initial Catalog=" + dbname + ";User ID={1};Password={2};", Form2.cmbServer.Text, Form2.txtUser.Text, Form2.txtPass.Text)
            Else
                Form2.objConn.ConnectionString = String.Format("Data Source={0}; Initial Catalog=" + dbname + "; Integrated Security=SSPI", Form2.cmbServer.Text)
            End If
            Try
                Form2.objConn.Open()
            Catch ex As Exception
                LogError(ex.Message, dbname, "")
            End Try
        End If

        Try
            Dim sqlcmd = "select name from sysobjects where xtype='P' and name not like 'dt_%'"

            Using cmd As New SqlCommand(sqlcmd, Form2.objConn)
                Using reader = cmd.ExecuteReader()
                    If reader.HasRows Then
                        While reader.Read()
                            procList.Add(reader("name").ToString())
                        End While
                    End If
                End Using
            End Using
        Catch ex As Exception
            LogError(ex.Message, dbname, "")
        End Try
        lblProcCount.Text = procList.Count
        proccount = procList.Count
        For c = 0 To procList.Count - 1
            lblProcNum.Text = c.ToString()
            lblProcName.Text = procList(c).ToString()
            Refresh()
            procupdate = False
            AlterProc(dbname, procList(c).ToString())
        Next
        If Form2.objConn.State = ConnectionState.Open Then Form2.objConn.Close()
    End Sub

    Public Sub AlterProc(ByVal dbname As String, ByVal procname As String)
        If Form2.objConn.State = ConnectionState.Closed Then
            If Form2.chkSSPI.Checked = False Then
                Form2.objConn.ConnectionString = String.Format("Data Source={0};Initial Catalog=" + dbname + ";User ID={1};Password={2};", Form2.cmbServer.Text, Form2.txtUser.Text, Form2.txtPass.Text)
            Else
                Form2.objConn.ConnectionString = String.Format("Data Source={0}; Initial Catalog=" + dbname + "; Integrated Security=SSPI", Form2.cmbServer.Text)
            End If
            Try
                Form2.objConn.Open()
            Catch ex As Exception
                LogError(ex.Message, dbname, "")
            End Try
        End If
        Try
            Dim sqlcmd = "select * from " + dbname + ".dbo.sysobjects o inner join " + dbname + ".dbo.syscomments c on o.id = c.id where name='" + procname + "'"

            Using cmd As New SqlCommand(sqlcmd, Form2.objConn)
                procText = ""
                Using reader = cmd.ExecuteReader()
                    If reader.HasRows Then
                        While reader.Read()
                            procText = procText + reader("text")
                        End While
                    End If
                End Using

                Dim arrProcData() = Split(procText, vbNewLine)

                Dim c As Integer
                procText = ""
                For c = 0 To UBound(arrProcData)
                    If InStr(UCase(arrProcData(c)), "CREATE") > 0 And InStr(UCase(arrProcData(c)), "PROCEDURE") > 0 Then
                        arrProcData(c) = Replace(Replace(Replace(arrProcData(c), "CREATE", "ALTER"), "create", "alter"), "Create", "Alter")
                    End If
                    If InStr(UCase(arrProcData(c)), UCase(txtFind.Text)) > 0 Then
                        arrProcData(c) = Replace(UCase(arrProcData(c)), UCase(txtFind.Text), UCase(txtReplace.Text))
                        replacecount += 1
                        procupdate = True
                    End If
                    procText = procText + arrProcData(c) + vbNewLine
                Next

            End Using
        Catch ex As Exception
            LogError(ex.Message, dbname, procname)
        End Try

        If procupdate = True Then
            Try
                Dim sqlcmd = procText
                Using cmd As New SqlCommand(sqlcmd, Form2.objConn)
                    cmd.ExecuteNonQuery()
                End Using
            Catch ex As Exception
                LogError(ex.Message, dbname, procname)
            End Try
        End If
    End Sub

    Private Sub frmDBList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        grpProgress.Visible = False
        Try
            Dim sqlcmd = "select name from master.dbo.sysdatabases where name not in ('msdb','master','temdb')"

            Using cmd As New SqlCommand(sqlcmd, Form2.objConn)
                Using reader = cmd.ExecuteReader()
                    If reader.HasRows Then
                        While reader.Read()
                            AddListBoxItem(reader("name").ToString(), CheckState.Unchecked)
                        End While
                    End If
                End Using
            End Using

        Catch ex As Exception
            LogError(ex.Message, "master", "")
        End Try
        Form2.objConn.Close()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Application.Exit()
    End Sub

    Private Sub btnCheckAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckAll.Click
        For i As Integer = 0 To clbDatabase.Items.Count - 1
            clbDatabase.SetItemChecked(i, CheckState.Checked)
        Next
    End Sub

    Private Sub btnUnCheckAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUnCheckAll.Click
        For i As Integer = 0 To clbDatabase.Items.Count - 1
            clbDatabase.SetItemChecked(i, CheckState.Unchecked)
        Next
    End Sub

End Class

Upvotes: 1

Related Questions