SQL Server Portal

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
GO
CREATE PROC test_Procedure
AS
SELECT * FROM sys.all_objects
GO

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
GO
SELECT O.name
,O.type_desc
,UCE.class_desc
,UCE.statement_type
,UCE.feature_name
,UCE.feature_type_name
FROM sys.dm_db_uncontained_entities AS UCE
LEFT JOIN sys.objects AS O
ON UCE.major_id = O.object_id
--OUTPUT

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