sys.dm_db_uncontained_entities (Transact-SQL)
Shows any uncontained objects used in the database. Uncontained objects are objects that cross the database boundary in a contained database. This view is accessible from both a contained database and a non-contained database. If sys.dm_db_uncontained_entities is empty, your database does not use any uncontained entities.
If a module crosses the database boundary more than once, only the first discovered crossing is reported.
Column name |
Type |
Description |
class |
int |
1 = Object or column (includes modules, XPs, views, synonyms, and tables). 4 = Database Principal 5 = Assembly 6 = Type 7 = Index (Full-text Index) 12 = Database DDL Trigger 19 = Route 30 = Audit Specification |
class_desc |
nvarchar(120) |
Description of class of the entity. One of the following to match the class.
|
major_id |
int |
ID of the entity. If class = 1, then object_id If class = 4, then sys.database_principals.principal_id. If class = 5, then sys.assemblies.assembly_id. If class = 6, then sys.types.user_type_id. If class = 7, then sys.indexes.index_id. If class = 12, then sys.triggers.object_id. |
statement_line_number |
int |
If the class is a module, returns the line number on which the uncontained use is located. Otherwise the value is null. |
statement_ offset_begin |
int |
If the class is a module, indicates, in bytes, beginning with 0, the starting position where uncontained use begins. Otherwise the return value is null. |
statement_ offset_end |
int |
If the class is a module, indicates, in bytes, starting with 0, the ending position of the uncontained use. A value of -1 indicates the end of the module. Otherwise the return value is null. |
statement_type |
nvarchar(512) |
The type of statement. |
feature_ name |
nvarchar(256) |
Returns the external name of the object. |
feature_type_name |
nvarchar(256) |
Returns the type of feature. |
Remarks
sys.dm_db_uncontained_entities shows those entities which can potentially cross the database boundary. It will return any user entities that have the potential to use objects outside of the database.
The following feature types are reported.
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
Security
Permissions
sys.dm_db_uncontained_entities only returns objects for which the user has some type of permission. To fully evaluate the containment of the database this function should be used by a high privileged user such as a member of the sysadmin fixed server role or the db_owner role.
Examples
The following example creates a procedure named P1, and then queries sys.dm_db_uncontained_entities. The query reports that P1 uses sys.endpoints which is outside of the database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE PROC P1
AS
SELECT * FROM sys.endpoints ;
GO
SELECT SO.name, UE.* FROM sys.dm_db_uncontained_entities AS UE
LEFT JOIN sys.objects AS SO
ON UE.major_id = SO.object_id;