Posts Tagged ‘sys.dm_db_uncontained_entities’

Today, I was working on contained database and came across with an issue (will post this issue in my upcoming post), where I had to find a list of uncontained objects (As per MSDN, uncontained objects are basically those objects that cross the database boundaries in a contained database.)

Also, as per MSDN, given below are the objects that usually cross the boundaries of the database:

  • Unknown containment behavior (dynamic SQL or deferred name resolution)
  • DBCC command
  • System stored procedure
  • System scalar function
  • System table valued function
  • System built-in function

But it is quiet difficult, if you search for any of the above objects in the database one by one. SQL Server resolves this issue by releasing a dynamic management view (sys.dm_db_uncontained_entities) that will give you all uncontained objects of any database, in one shot.

Let me create an uncontained objects in a contained database.

--Create a stored procdure by using sys.allobjects
USE AdventureWorks2012
CREATE PROC test_Procedure
SELECT * FROM sys.all_objects

Once you created the above stored procedure, just execute the given below script it will give you the name of above procedure along with other uncontained objects.

 Use AdventureWorks2012
FROM sys.dm_db_uncontained_entities AS UCE
LEFT JOIN sys.objects AS O
ON UCE.major_id = O.object_id


Note : If the above query return Nil(or emplty) it means that you donot have any uncontained objects.

Read Full Post »