Generate sysdepends list

:shocked: I can't seem to find a way of gettng a list of the view/stored procedure/UDF dependencies by object. Any ideas?What program are you using to do this?|||I am using ASP and vbscript to access the list and it is not retreiving all of the dependencies.:eek:|||Microsoft SQL doesn't guarantee 100% coverage for sysdepends. There will be some cases where you won't have a row that should be there.

-PatP|||Thanks Pat. I found shrinking the database brings newer dependencies into the list. Odd.:shocked:|||If a DDL script creates an object that references an object that does not yet exist (for instance, it is created later on...), then the first object will be created anyway but an entry will not be added to sysdepends. It should generate a warning to notify you of this.|||I create any dependencies a new store procedure, UDF or view needs first before creating the new piece to avoid the errors associated with referenced unknowns. Thanks for the tip.:)

Generate Alter Script

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

Generate Alter Script

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