Microsoft – SQL Server – Identify “uncontained” objects

Background

Unlike some of the other relational databases i.e. Oracle and DB/2, SQL Server and Sybase programmable universe stretches beyond individual database containers into databases that share the same SQL Instance.

In essence, database programmable objects such as a Stored Procedures, views, functions, and triggers can reference objects in other databases.

 

Contained Databases

Starting with SQL Server 2012, Microsoft introduces the concept of Contained databases.

Here is what MS documentation says about the rationale:

Contained Databases
http://msdn.microsoft.com/en-us/library/ff929071.aspx

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server helps user to isolate their database from the instance in these ways:

  • Much of the metadata that describes a database is maintained in the database.  In addition to, or instead of, maintaining metadata in the master database
  • All metadata are defined using the same collation.
  • User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
  • The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.

 

Contained Databases – Benefits

Programming towards full database containment offers benefits such as:

  • Easier to move such databases from one instance to another
  • Easier to move from one hosts to another during fail-over knowing that there is less dependency on the actual host and other residing databases
  • Localize management role

 

Identify Objects that are not fully contained

Knowing the potential benefits of full containment, let us see how we can determine which objects have dependencies on other objects outsize their database.

As always, Microsoft’s has come to other aid.

There are a set of tools:

  • Dynamic Management Views
    • Uncontained Entities
      • sys.dm_db_uncontained_entities ( This view shows any entities in the database that have the potential to be uncontained, such as those that cross-the database boundary. This includes those user entities that may use objects outside the database model. )
    • Code Definition
      • sys.sql_modules ( This views contains the programmable object’s code definition )
      • sys.default_constraints ( Default Constraint code definition )

 

 

Code

Pasted below is a set of SQL code.

The code rests fairly heavily on a splitter code.  The splitter code separates out the module entry for each object into separates lines.   There are various such code on the Net.

Here are some examples:

 

For this exercise, we chose to use the Table Value Function from anvil-of-time.com

The first code, Code-1, queries the aforementioned views and lists un-contained entities.  But, unfortunately it sometime breaks with the error pasted below:

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

 

The second code, Code-2, relies on a loop and this seems to protect it from the brittleness of Code-1.

Code-1

set nocount on;

declare @newline nvarchar(30)

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

select

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

         when (tblUCE.class = 1) then
             object_schema_name(tblUCE.major_id)
                + '.' + object_name(tblUCE.major_id)

         else cast(null 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 [dbo].[uft_splitnotes](tblSM.[definition], @newLine) tblSQLText

 -- match line number and sql text seq no
 where tblUCE.statement_line_number = tblSQLText.SeqNo

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

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

 order by 

      case

           when (tblUCE.class = 1) then
               object_schema_name(tblUCE.major_id)
                 + '.' + object_name(tblUCE.major_id)

           else cast(null as sysname)

     end asc

     , tblUCE.statement_line_number asc

Code-2

 


set nocount on;

declare @newLine nvarchar(30)
set @newline = nchar(13) + nchar(10)

declare @tblCache TABLE
(
[id] int not null identity(1,1)
, [class] int not null
, [classDescription] sysname
, [objectID] int not null
, [objectName] sysname null
, statementLineNumber int null
, [definition] nvarchar(max) null
, [definitionLength] int null
, [SQLLine] nvarchar(4000) null

)

declare @id int
declare @idMax int
declare @definition nvarchar(max)
declare @statementLineNumber int
declare @sqlLine nvarchar(4000)

set @id = 1

insert into @tblCache
(
[class]
, [classDescription]
, [objectID]
, [objectName]
, statementLineNumber
, [definition]
, [definitionLength]
)
select
       tblUCE.class
     , tblUCE.class_desc
     , tblUCE.major_id as objectID
     , case

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

       end as [entity]
   , tblUCE.statement_line_number
   , tblSM.[definition]
   , len(tblSM.[definition])

from sys.dm_db_uncontained_entities tblUCE

      left outer join sys.sql_modules tblSM

         on tblUCE.major_id = tblSM.object_id

         --http://msdn.microsoft.com/en-us/library/ff929336.aspx
         --1 = Object or column (includes modules, XPs, views, synonyms, and tables).
         and tblUCE.class = 1

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

      left outer join sys.objects tblO

         on tblSM.object_id = tblO.object_id

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

set @idMax= @@rowcount
set @id =1

while (@id <= @idMax)
begin

     /* Get Definition and "offending SQL Line Number */
     select
              @definition = tblC.[definition]
            , @statementLineNumber = tblC.statementLineNumber
     from @tblCache tblC
     where [id] = @id

     /* Get SQL Line */
     select @sqlLine = tblSQLText.Item
     from [dbo].[uft_splitnotes](@definition, @newLine) tblSQLText
     where tblSQLText.SeqNo = @statementLineNumber

    update tblC
    set tblC.SQLLine = @sqlLine
    from @tblCache tblC
    where tblC.[id] = @id

    set @id = @id + 1

end  -- while

select tblC.*

from @tblCache tblC

order by
          tblC.objectName
        , tblC.statementLineNumber

go

 

Microsoft Connect

This morning I opened up a Microsoft Connect entry (#1053921) to help track and solicit community feedback per the bug I aforementioned.

 

Addendum

2014-Dec-16 Tuesday

  • Posted a follow-up post at Microsoft – SQL Server – Identify “uncontained” objects — Using SQLCLR to split strings ( https://danieladeniji.wordpress.com/2014/12/16/32846/ ).  Interestingly enough we are able to avoid the problem by using Adam Mechanic’s SQLCLR function.
  • Via email, Erland Sommarskog responded back to my email and confirmed that he is able to reproduce the problem.  Like  I always say there is no better community in the world.

 

2016-march 25th to March 29th

Microsoft’s Sergey Ten worked and resolved issue.

Like that guy who will give his right arm to be an ambidextrous, I would do same to be part of SQL community.

One thought on “Microsoft – SQL Server – Identify “uncontained” objects

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 )

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