Replace sp_msforeachtable With the CLR

There is an undocumented system stored procedure, sp_msforeachtable, that allows you to pass a query to all user tables in a database.  Unfortunately since this stored procedure is undocumented it can be pulled from existence at any time.  There are several ways to replicate this functionality using T-SQL, but this post will focus on using the CLR.

In order to keep things as consistent as possible I created a CLR stored procedure and kept the convention of using a “?” as the placeholder for the table name.  The VB code looks like this:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub clr_foreach_table(ByVal command As SqlString, ByVal Type As SqlString)

'Create the output that will be used to return erros that are caught in the upcoming code
        Dim Output As SqlPipe = SqlContext.Pipe()
        Try
'Create a connection to pass the query
  Dim conn As New SqlConnection("context connection=true")
'create a command from the current context
  Dim cmd As SqlCommand = conn.CreateCommand()
'Based on the type parameter passed the meta data will be searched for all tables and views, only tables, or only views
  Select Case Type.ToString.ToUpper
      Case "*"
cmd.CommandText = "SET NOCOUNT ON SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
      Case "T"
cmd.CommandText = "SET NOCOUNT ON SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"
      Case "V"
cmd.CommandText = "SET NOCOUNT ON SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW'"
 End Select
 conn.Open()
'Create a data adapted and a data table that will hold the results of the query for the tables and/or views
  Dim da As New SqlDataAdapter(cmd)
  Dim dt As New DataTable
'Fill the data table with the results and then close the connection and dispose of both the connection and data adapter
  da.Fill(dt)
  da.Dispose()
  conn.Close()
  conn.Dispose()
'Create a counter to use in a While loop to dynamically create the statement to iterate through each command
  Dim counter As Integer
  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
 Dim cn As New SqlConnection("context connection=true")
'Create a string that will hold the query with the replaced question mark holding the table and/or view
 Try
'Replace the question mark with the table/view name and assign the new query text

 Dim sp_command As New SqlCommand(command.ToString.Replace("?", dt.Rows(counter)(0).ToString), cn)
      cn.Open()
      SqlContext.Pipe.ExecuteAndSend(sp_command)
        cn.Close()
Catch ex As Exception
'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
       End Try
End While

  Catch ex As Exception
'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
     End Try
   End Sub
End Class


The proc. accepts two input parameters, the first being the query to be passed to each table (the place holder for the table name is a “?”), and a parameter that can be used to specify whether the query should be: 1. Used for all user tables and user defined views, “*”

2. Only user tables, “T”

3. Only user defined views, “V” 

The parameters are called “command” and “type” and are both a sqlstring data type.

A variable is created called “output”, as SQLPipe, that is used to pipe any errors caught in the code.

A connection is created called “conn”, using the context connection, and a SQL command is created called “cmd” using Select Case and evaluating the “type” parameter passed.   The command uses the INFORMATION_SCHEMA.TABLES view and the WHERE clause is used to filter for tables only, “BASE_TABLE”, or views, “VIEW”. 

****Just a note that the sp_msforeachtable only passes the query to user tables and can not include views.

A data table is populated to hold the schema and table/view names which is used to dynamically build the command. 

****A data table is used because after some testing this provided less overhead than using a loop to iterate through the results of the “cmd”, replace the place holder with the resulting schema.table name and passing this back to sql.

A variable called “counter” is created and assigned the row count of the data table.  Using a While loop the command is created using the input parameter “command” and the “?” place holder is replaced with the table name from the data table. 

A new connection is created using the context connection and used in the instantiation of the a SQLCommand variable called “sp_command”.  The query is then executed using SqlContext.Pipe.ExecuteAndSend.

Within the Catch I included the “output” to pipe any error messages back and included the location of the catch. 

Once the CLR stored procedure is deployed it can be called passing a command and the type filter:

clr_foreach_table 'SELECT * FROM ?', 'T'

A default value can be added to the “type” parameter by altering the procedure:

ALTER PROCEDURE clr_foreach_table
   @command [nvarchar](4000),
   @type [nvarchar](4000) = '*'
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [clr_foreach_table].[clr_foreach_table.StoredProcedures].[clr_foreach_table]
GO



After executing both this CLR stored procedure alongside sp_msforeachtable the execution time and resources used are close to identical when specifying “T” for the type parameter in the CLR procedure.

The C# code will be posted later this week.