Hi,
I read some related threads but none seem to answer my question. How to script a udf or a store procedure that exists in my database as an ALTER script. I don't want to modify the udf to be able to capture it(captureSql). I just want to script it as an ALTER. The scriptingOptions contains the DROP but I did not see the ALTER. I know SSMS does that when I right click on a store procedure I have the option to script it as ALTER.
I am guessing I can get the script then replace the CREATE with ALTER but that does not look to me a good solution. I am hoping there is something in SMO I can use.
Thanks,
Elie
Source Code Sample:
Code Block
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Threading;
using System.Collections.Specialized;
using System.IO;
using System.Text.RegularExpressions;
namespace SMOEvents
{
class SmoEvents
{
private static Server server = null;
private static Database db = null;
private static string folderPath = "";
private static string procedureNamePattern = "";
private static string fileNameExnention = "";
static void Main(string[] args)
{
#region config data
string connString = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None).ConnectionStrings.ConnectionStrings["MSSqlServer"].ToString();
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connString);
if (!builder.IntegratedSecurity)
{
SqlConnectionInfo ci = new SqlConnectionInfo(builder.DataSource, builder.UserID, builder.Password);
ServerConnection serverConn = new ServerConnection(ci);
server = new Server(serverConn);
}
else
{
server = new Server(builder.DataSource);
}
if (builder.InitialCatalog != "")
{
db = server.Databases[builder.InitialCatalog];
}
else {
db = server.Databases[0];
}
procedureNamePattern = ConfigurationManager.AppSettings.GetValues("ProcedureNamePattern").GetValue(0).ToString();
folderPath = ConfigurationManager.AppSettings.GetValues("LocalFolderPath").GetValue(0).ToString();
fileNameExnention = ConfigurationManager.AppSettings.GetValues("FileNameExnention").GetValue(0).ToString();
#endregion
if (args.Length > 0)
{
if (args[0].ToString() == "ListenEvents")
{
ListenEvents();
}
else
{
ScriptAllProcedures();
return;
}
}
else {
ScriptAllProcedures();
return;
}
}
private static void ScriptAllProcedures()
{
Regex rx = new Regex(procedureNamePattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
foreach (StoredProcedure sp in db.StoredProcedures)
{
if ((!sp.IsEncrypted) && (!sp.IsSystemObject) && (rx.IsMatch(sp.Name)))
{
string fileName = folderPath + sp.Schema + "." + sp.Name + fileNameExnention;
ScriptProcedure(sp, fileName);
}
}
}
private static void ScriptProcedure(StoredProcedure sp, string fileName)
{
FileInfo fi = new FileInfo(fileName);
if ((!fi.Exists) ||(fi.Exists && !fi.IsReadOnly))
{
string body = "IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + sp.Schema + "].[" + sp.Name + "]') AND type in (N'P', N'PC'))";
body = body + "\r\n" + " DROP PROCEDURE [" + sp.Schema + "].[" + sp.Name + "]";
body = body + "\r\n" + "GO\n";
body = body + "\r\n" + "SET ANSI_NULLS ON";
body = body + "\r\n" + "GO";
body = body + "\r\n" + "SET QUOTED_IDENTIFIER ON";
body = body + "\r\n" + "GO";
body = body + "\r\n";
body = body + "\r\n" + sp.TextHeader;
body = body + "\r\n" + sp.TextBody;
body = body + "\r\n" + "GO";
body = body + "\r\n";
body = body + "\r\n" + "SET ANSI_NULLS OFF";
body = body + "\r\n" + "GO";
body = body + "\r\n" + "SET QUOTED_IDENTIFIER OFF";
body = body + "\r\n" + "GO";
File.WriteAllText(fileName, body, UnicodeEncoding.Unicode);
Console.WriteLine(@."{0} ", fileName);
}
}
private static void ListenEvents()
{
db.Events.ServerEvent += new ServerEventHandler(Events_ServerEvent);
// DDL events
db.Events.SubscribeToEvents(DatabaseEvent.AlterProcedure + DatabaseEvent.CreateProcedure);
Console.WriteLine(@."Starting events for server: {0}", server.Name);
Console.WriteLine();
Console.WriteLine(@."(Hit Ctrl-C or Ctrl-Break to quit.)");
//Console.WriteLine(@."Press any key to continue.");
//Console.ReadKey();
Console.WriteLine(@."Start listening: {0}", DateTime.Now);
// Start receiving events
db.Events.StartEvents();
// Wait indefinitely for events to occur
Thread.Sleep(Timeout.Infinite);
Console.WriteLine(@."Stop listening ...");
// Unsubscribe from all the events when finished
server.Events.UnsubscribeAllEvents();
}
/// <summary>
/// to avoid Access Denied Error you should execute the following script:
/// ALTER DATABASE [@.dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/// go
/// ALTER DATABASE [@.dbname] SET ENABLE_BROKER
/// go
/// ALTER DATABASE [@.dbname] SET MULTI_USER
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
static void Events_ServerEvent(object sender, ServerEventArgs e)
{
try
{
if (e.Properties["ObjectName"] != null)
{
StoredProcedure sp = db.StoredProcedures[e.Properties["ObjectName"].Value.ToString()];
if ((!sp.IsEncrypted) && (!sp.IsSystemObject))
{
string fileName = folderPath + sp.Schema + "." + sp.Name + fileNameExnention;
ScriptProcedure(sp, fileName);
}
}
Console.WriteLine(@."EventType: {0, -20} SPID: {1, 4} PostTime: {2, -20}", e.EventType, e.Spid, e.PostTime);
Console.WriteLine();
}
catch (ApplicationException ex)
{
Console.WriteLine(ex.ToString());
}
}
}
}
App.Config Sample:
Code Block
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ProcedureNamePattern" value=".+"/>
<add key="FileNameExnention" value=".sql"/>
<add key="LocalFolderPath" value=".\Stored Procedures\"/>
</appSettings>
<connectionStrings>
<add name="MSSqlServer" connectionString="Data Source=.;Integrated Security=true;Initial Catalog=AdventureWorks;" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
|||
Thanks for the code.
I tried the code with the Listenevents option but I am getting an error: ProviderLoadFailure
on this line db.Events.StartEvents();
The ScriptProcedure() does script it as create. But I am looking for a way to be scripted as Alter.
Thanks
Elie
|||
Scripting with CREATE and replacing the CREATE with ALTER I believe is the only way you can get it done with SMO. There is not a native ALTER creation in SMO (that is documented).
|||
Thanks I thought so. I wish I found the documentation.
Elie
