SQL Server – Identify “uncontained” objects — Using SQLCLR to split strings


This is a quick followup to our last post; that post is titled Microsoft – SQL Server – Identify “uncontained” objects ( Link ).

In the post mentioned something about a concerning error :

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.


Technical Discussions

The problem stayed on my mind and took me back to a good and informed set of technical discussions:

  1. Tally OH! An Improved SQL 8K “CSV Splitter” Function
  2. T-SQL Queries – Split Strings


The discussions mentioned that the best path to splitting strings is to use SQL CLR.  And, so I decided to try out that path.


Adam Machanic

Found out from Aaron Bertrand  that Adam Machanic of the WhoisActiveLink ) fame has a well written SQL CLR function that splits strings, as well.

Downloaded Adam’s code from here.

And, started to use it, but quickly found out that it is missing Row Numbers.  Thankfully Surya Pratap laid out a nice trail on how to add “Row Numbers” to the returned table.


Edited Code

Here is Adam’s original code with Surya’ hints ( added in ).


using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class ResultSet

	public readonly int row;

	public readonly string value;

	public ResultSet(int row, string value)

		this.row = row;

		this.value = value;



public partial class UserDefinedFunctions
       FillRowMethodName = "FillRow_Multi",
       TableDefinition = "item nvarchar(4000)"
    public static IEnumerator SplitString_Multi(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));

    //public static void FillRow_Multi(object obj, out SqlString item)
	public static void FillRow_Multi(object obj, out SqlInt32 row, out SqlString item)

        //item = new SqlString((string)obj);

		ResultSet rs = (ResultSet)obj;

		row = new SqlInt32(rs.row);

		item = new SqlString(rs.value);


    public class SplitStringMulti : IEnumerator

		private int row;

        public SplitStringMulti(char[] TheString, char[] Delimiter)
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;

			//initalise row in "Constructor" and during "Reset" set row to 0 by adding
			row = 0;

        #region IEnumerator Members

        public object Current
                //return new string(theString, lastPos, nextPos - lastPos);

				String strToken = null;
				ResultSet objResultSet = null;
				strToken = new string (theString, lastPos, nextPos - lastPos);

				objResultSet = new ResultSet(++row, strToken );

				return (objResultSet);


        public bool MoveNext()
            if (nextPos >= stringLen)
                return false;
                lastPos = nextPos + delimiterLen;

                for (int i = lastPos; i  0)
                            return true;
                            i += (delimiterLen-1);
                            lastPos += delimiterLen;

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                    return false;

        public void Reset()
            lastPos = 0;
            nextPos = delimiterLen * -1;

			//initialise row in "Constructor" and during "Reset" set row to 0 by adding
			row = 0;


        private int lastPos;
        private int nextPos;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;


And, to compile it


   rem http://msdn.microsoft.com/en-us/library/78f4aasd.aspx

   rem C:\windows\Microsoft.NET\Framework\v4.0.30319\csc /out:CLRUtilities.dll /target:library SplitStringMulti.cs
   C:\windows\Microsoft.NET\Framework\v4.0.30319\csc /out:CLRUtilitiesIdentity.dll /target:library SplitStringMultiIdentity.cs

To use the CLR in SQL Server


   use master

   declare @filename sysname
   set @filename = 'C:\AdamMachnic\CLRUtilitiesIdentity.dll'

     DROP FUNCTION dbo.SplitStrings_CLR

   CREATE ASSEMBLY CLRUtilities FROM @filename

   CREATE FUNCTION dbo.SplitStrings_CLR
      @List NVARCHAR(MAX),
     @Delimiter NVARCHAR(255)
   RETURNS TABLE ( Row int, Item NVARCHAR(4000) )
   EXTERNAL NAME CLRUtilities.UserDefinedFunctions.SplitString_Multi;

   exec sp_configure 'show advanced options',1
   reconfigure with override

   exec sp_configure 'clr enabled',1
   reconfigure with override


Try Things Out


Here is our SQL Code for trying things out.


     declare @listofFruits nvarchar(600)
     declare @listSeparator nvarchar(30)

     set @listofFruits = 'Apple, Orange, Pear, Banana, Mango'
     set @listSeparator = ','

     select *
     from   [master].dbo.SplitStrings_CLR(@listofFruits, @listSeparator)





Utilize Code


Here is the SQL Code where we utilize Adam’s code.


    --Adam Machanic

declare @newline nvarchar(30)

set @newline = char(13) + char(10)


        , tblUCE.class_desc
        , tblUCE.major_id as objectID
        , case

                when (tblUCE.class = 1) then
                        + '.' + object_name(tblUCE.major_id)
                else cast(tblUCE.major_id as sysname)

           end as [entity] 

        , tblUCE.statement_line_number

        , len(tblSM.[definition]) as lengthOFDefinition

        ,  tblSM.[definition]

        , tblSQLText.[Item] as sqlLine

from   sys.dm_db_uncontained_entities tblUCE

          inner join sys.sql_modules tblSM

                on tblUCE.major_id = tblSM.object_id

          inner join sys.objects tblO

               on tblSM.object_id = tblO.object_id

         cross apply [master].dbo.SplitStrings_CLR(tblSM.[definition], @newLine)

--filter out encryped objects
where tblSM.[definition] is not null

--filter out Microsoft shipped\owned objects
and   tblO.is_ms_shipped = 0

--match line numbers
and tblUCE.statement_line_number = tblSQLText.[Row]

order by 


                when (tblUCE.class = 1) then
                        + '.' + object_name(tblUCE.major_id)
                else cast(tblUCE.major_id as sysname)

         ,  tblUCE.statement_line_number desc

Things worked out.  No errors this time.


Source Control


Files posted to GitHub @ here



Dedicated to the public square commiters — Erland Sommarskog, Steve Jones, Aaron Bertrand, Adam Machanic, and Jeff Moden.


Listening To

And, listening to that old song

John Michael Montgomery – Letters from Home

3 thoughts on “SQL Server – Identify “uncontained” objects — Using SQLCLR to split strings

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s