Edit

sys.dm_db_uncontained_entities (Transact-SQL)

  • Article

Applies to: SQL Server

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:

OBJECT_OR_COLUMN

DATABASE_PRINCIPAL

ASSEMBLY

TYPE

INDEX

DATABASE_DDL_TRIGGER

ROUTE

AUDIT_SPECIFICATION
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.

If class = 19, then sys.routes.route_id.

If class = 30, then sys. database_audit_specifications.database_specification_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;

See Also

Contained Databases