Tuesday, December 21, 2010

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;
  
}
      }  
        dt.Dispose
();
         
}      
                    
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 outer Try block
      
output.Send("Outer Try " + ex.Message.ToString());
  
}
       
finally
                
{
            }
          }
   }
;

0 comments:

Post a Comment