SQL Multi Script by Redgate distribution list generator

The following code generates a custom distribution list for SQL Multi Script by Redgate for version 1.1.0.34.

We have a lot of databases and several servers that change constantly, and I got sick of updating the distribution list each time I ran the program, so I came up with this that runs each time before launching Multi Script.  It is a VB.NET code file and the second part is a class file.  Just set up a new VB application project, add this code file and class file, edit as needed and compile.

• You will have to modify SQLExecT for your environment
• Repeat the Begin Distribution List section for each distribution list needed, each with a unique guid and name.
• We store a list of all databases and which server they are on in a table called CustomerDatabases. You will have to customize this to your own environment.
• The DENSE_RANK() allows me to order the databases in a custom way so I get better parallel execution.
• It requires 3 arguments.  The first is the file path to the Multi Script distribution list xmlx/dat file, the second is the username that Multi Script will use, the third is the encrypted password that you will need to get out of the Multi Script distribution file before you generate your own.
• Depending on OS, the filepath should be something like: C:\Users\XXXX\AppData\Roaming\Red Gate\SQL Multi Script\Application.dat

Imports System
Imports System.Net
Imports System.IO
Imports System.Xml
Imports Microsoft.VisualBasic
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports MultiScript.CWDBUtil
Module Module1
   Sub Main(ByVal args As String())
      Dim dt As DataTable, dt2 As DataTable, sSQL As String = "", username As String = "", password_encrypted As String = ""
      Dim server_name As String = "", database_name As String = "", environment As String = "", nguid As String = "", filepath As String = ""
      If args.Length > 0 Then
         filepath = args(0)
      Else
         filepath = "C:\Users\Dave\AppData\Roaming\Red Gate\SQL Multi Script\Application.dat"
      End If
      If args.Length > 1 Then
         username = args(1)
      Else
         username = "myusername"
      End If
      If args.Length > 2 Then
         password_encrypted = args(2)
      Else
         password_encrypted = "xxxxxxxxxxxxx"
      End If
      File.Delete(filepath)
      Dim fs As FileStream = New FileStream(filepath, FileMode.OpenOrCreate, FileAccess.Write)
      Dim s As StreamWriter = New StreamWriter(fs, Encoding.Unicode)
      s.Write("<?xml version=""1.0"" encoding=""utf-16"" standalone=""yes""?>")
      s.Write("<!--" & vbCrLf & "SQL Multi Script 1" & vbCrLf & "SQL Multi Script" & vbCrLf & "Version:1.1.0.34-->")
      s.Write("<multiScriptApplication version=""2"" type=""multiScriptApplication"">")
      s.Write("<databaseLists type=""List_databaseList"" version=""1"">")
      '-- Repeat the following section for each distribution list needed, each with a unique guid and name.
      '-- Begin distribution list 1
      s.Write("<value type=""databaseList"" version=""2"">")
      s.Write("<name>*ALL LIVE DATABASES</name>")
      s.Write("<databases type=""BindingList_database"" version=""1"">")
      sSQL = "SELECT server_name, database_name, nguid = Convert(Varchar(50), NEWID()) FROM CustomerDatabases " & _
             "WHERE is_live = 1 ORDER BY DENSE_RANK() OVER (PARTITION BY server_name ORDER BY customer_id), server_name"
      dt = SQLExecT(sSQL)
      For Each row As DataRow In dt.Rows
         server_name = row("server_name")
         database_name = row("database_name")
         nguid = row("nguid")
         s.Write("<value type=""database"" version=""5"">")
         s.Write("<name>" & database_name & "</name>")
         s.Write("<server>" & server_name & "</server>")
         s.Write("<integratedSecurity>False</integratedSecurity><username>" & username & "</username><savePassword>True</savePassword><password encrypted=""1"">" & password_encrypted & "</password>")
         s.Write("<connectionTimeout>15</connectionTimeout><protocol>-1</protocol><packetSize>4096</packetSize><encrypted>False</encrypted><selected>True</selected>")
         s.Write("<cserver>" & server_name & "</cserver></value>")
      Next
      dt.Dispose()
      s.Write("</databases>")
      s.Write("<guid>" & nguid & "</guid>")
      s.Write("</value>")
      '-- End distribution list 1
      s.Write("</databaseLists>")
      s.Write("<addedServers type=""List_server"" version=""1"">")
      sSQL = "SELECT DISTINCT server_name FROM CustomerDatabases ORDER BY server_name"
      dt = SQLExecT(sSQL)
      For Each row As DataRow In dt.Rows
         server_name = row("server_name")
         s.Write("<value type=""server"" version=""1""><name>" & server_name & "</name><integratedSecurity>False</integratedSecurity><username>" & username & "</username><savePassword>True</savePassword><password encrypted=""1"">" & password_encrypted & "</password><connectionTimeout>15</connectionTimeout><protocol>-1</protocol><packetSize>4096</packetSize><encrypted>False</encrypted></value>")
      Next
      dt.Dispose()
      s.Write("</addedServers>")
      s.Write("</multiScriptApplication>")
      s.Close()
      fs.Close()
      s.Dispose()
      fs.Dispose()
      System.Diagnostics.Process.Start("C:\Program Files\Red Gate\SQL Multi Script 1\SQLMultiScript.exe")
   End Sub

End Module


-----------------------------------------------------------------------
-----------------------------------------------------------------------

Imports Microsoft.VisualBasic
Imports System.Text
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net.Mail
Public Class CWDBUtil
   ''' <summary>Opens a database connection if it is not already open</summary>
   ''' <param name="DataSource">Server name</param>
   Public Shared Function OpenSqlConn(ByVal DataSource As String) As SqlConnection
      Dim userid As String = "sa"
      Dim password As String = "xxxxxxxx"
      Dim dbConn As SqlConnection
      dbConn = New SqlConnection("Data Source=" & DataSource & ";Initial Catalog=Master;Persist Security Info=True;User ID=" & userid & ";Password=" & password)
      dbConn.Open()
      Return dbConn
   End Function
   ''' <summary>Closes an sql connection</summary>
   Public Shared Sub CloseSqlConn(ByRef dbConn As SqlConnection)
      If Not dbConn Is Nothing Then
         Try
            If dbConn.State = ConnectionState.Open Then dbConn.Close()
         Catch
         End Try
         dbConn = Nothing
      End If
   End Sub
   ''' <summary>Runs an SQL Statement and returns a DataTable.</summary>
   ''' <param name="sSQL">An SQL string</param>
   ''' <param name="DataSource">Server name</param>
   Public Shared Function SQLExecT(ByVal sSQL As String, ByVal DataSource As String) As DataTable
      Dim adpt As SqlClient.SqlDataAdapter, dt As DataTable
      Dim cmnd As SqlCommand = Nothing, dbConn As SqlConnection = Nothing
      dbConn = OpenSqlConn(DataSource)
      Try
         cmnd = New SqlCommand(sSQL, dbConn)
         adpt = New SqlClient.SqlDataAdapter(cmnd)
         dt = New DataTable
         adpt.Fill(dt)
         SQLExecT = dt
         CloseSqlConn(dbConn)
      Catch e As SqlException
         CloseSqlConn(dbConn)
         Throw
      End Try
   End Function
End Class 

SQL Search Function

Here is a SQL Search procedure that will search syscomments for any text in stored procedures, triggers, funtions, views, etc. for a single database.  It supports ! for NOT LIKE and | for OR as the first character of a search parameter.  It uses the flattening technique on syscoments because SQL Server will split a long stored procedure into multiple rows, and this allows a search across the row boundaries.  I also went with dynamic SQL because it gave me better speed and flexibility.  I wasn't able to come up with a good grouping mechanism using parens when mixing ANDs and ORs, so just remember AND has a higher precedence than OR.  It currently excludes defaults, so take out the AND so.xtype IN line to include these.  If you have very long sprocs, uncomment out the SET @maxrow line.

CREATE Procedure [dbo].[uspSearch]

  @s1 Varchar(255), -- Search parameter

  @s2 Varchar(255) = NULL,

  @s3 Varchar(255) = NULL,

  @s4 Varchar(255) = NULL,

  @s5 Varchar(255) = NULL,

  @show_text BIT = 0 -- Outputs the text of the procedure

AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF

-- -----------------------------------------------------------------------------------

--  Purpose: Search all stored procedure for given text parameters.  Uses LIKE search.

--    Notes: Use ! for NOT LIKE or | for OR as the first character of any search param

-- Creation: DW 04/11

-- -----------------------------------------------------------------------------------

DECLARE @sql NVarchar(Max), @i INT = 1, @s Varchar(255), @maxrow INT = 15

IF @s1 IS NULL RETURN

--SET @maxrow = (SELECT MAX(colid) FROM syscomments)

SET @sql = '

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT [name], xtype '

IF @show_text = 1 SET @sql = @sql + ', Char(10) + stext AS stext '

SET @sql = @sql + '

  FROM (

SELECT Max(so.name) AS [name], MAX(so.xtype) AS xtype, stext = '

WHILE @i <= @maxrow

BEGIN

  SET @sql = @sql + 'CONVERT(VARCHAR(MAX), MAX(CASE WHEN sc.colid = ' +

    CONVERT(VARCHAR, @i) + ' THEN sc.text END)) + '

  SET @i = @i + 1

END

SET @sql = @sql + ' NULL

  FROM syscomments sc JOIN sysobjects so ON sc.id = so.id

 WHERE sc.text IS NOT NULL

   AND so.xtype IN (''P'',''TR'',''FN'',''TF'',''C'',''V'')

 GROUP BY sc.id

) t WHERE 1 = 1 '

SET @i = 1

WHILE @i <= 5

BEGIN

  SET @s = CASE @i WHEN 1 THEN @s1 WHEN 2 THEN @s2 WHEN 3 THEN @s3

           WHEN 4 THEN @s4 WHEN 5 THEN @s5 END

  IF @s IS NOT NULL

  BEGIN

    IF LEFT(@s, 1) = '!'

      SET @sql = @sql + '

   AND stext NOT LIKE ' + QUOTENAME('%' + SUBSTRING(@s, 2, 254) + '%', '''')

    ELSE IF LEFT(@s, 1) = '|'

      SET @sql = @sql + '

    OR stext LIKE ' + QUOTENAME('%' + SUBSTRING(@s, 2, 254) + '%', '''')

    ELSE

      SET @sql = @sql + '

   AND stext LIKE ' + QUOTENAME('%' + @s + '%', '''')

  END

  SET @i = @i + 1

END

SET @sql = @sql + '

 ORDER BY xtype, [name]'

--PRINT @sql

EXECUTE sp_executesql @sql