Replace sp_msforeachtable With the CLR C#

In a previous post I create a CLR stored procedure in VB.NET to replace the undocumented system stored procedure sp_msforeachtable.  As promised here is the C# code.  I am still working on reacquainting myself with C based syntax so please feel free to point out any errors or performance issues.

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures
{
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void clr_foreach_table(SqlString command, SqlString type)
   { //Create the output that will be used to return erros that are caught in the upcoming code
     SqlPipe  output  = SqlContext.Pipe;
      try
         {
             SqlConnection conn = new SqlConnection("context connection=true");
             SqlCommand cmd = conn.CreateCommand(); //Create a string that will hold the query with the replaced question mark holding the table and/or view
  //Dim foreach As String = Nothing
   switch (type.ToString().ToUpper())
   {
  case "*":
      cmd.CommandText = "SET NOCOUNT ON SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
      break;
  case "T":
      cmd.CommandText = "SET NOCOUNT ON SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
      break;
  case "V":
      cmd.CommandText = "SET NOCOUNT ON SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW'";
      break;
   }
   conn.Open();
   SqlDataAdapter da = new SqlDataAdapter(cmd);
   DataTable dt = new DataTable();
             da.Fill(dt);
             da.Dispose();
             conn.Close();
             conn.Dispose();
             int counter = dt.Rows.Count-1;
            while (counter >= 0)
            { //A new context connection is created as the previous has been used with a different command and then disposed
  SqlConnection cn = new SqlConnection("context connection=true");
  try
   { //Replace the question mark with the table/view name and assign the new query to the foreach variable
//foreach = command.ToString.Replace("?", dt.Rows(counter)(0).ToString)
//Create a sql command that is passed the foreach string and the new connection
       SqlCommand sp_command = new SqlCommand(command.ToString().Replace("?", dt.Rows[counter][0].ToString()), cn);
      cn.Open();
      SqlContext.Pipe.ExecuteAndSend(sp_command);
      cn.Close();
  }
   catch (Exception ex)
   {
      //Catch any error(s) and then use the output to pipe this to the messages of SSMS specifying that it is
      //in the inner Try block and also pass the query being executed back to troubleshoot
       output.Send("Inner Try " + ex.Message.ToString() + command.ToString().Replace("?", dt.Rows[counter][0].ToString()));
  }
   finally
   {
      cn.Close();
      counter -= 1;
  }

Read more: SQLServer pedia

Posted via email from .NET Info