Microsoft – 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 ( ).

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:



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 WhoisActive ) fame has a well written SQL CLR function that splits strings, as well.

Downloaded Adam’s code ( from ).

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 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 @



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 “Microsoft – 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: Logo

You are commenting using your 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 )

Google+ photo

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

Connecting to %s