TechNet
Export (0) Print
Expand All

sys.dm_db_uncontained_entities (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

Applies to: SQL Server (SQL Server 2012 through current version).
Column nameTypeDescription
classint1 = 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_descnvarchar(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_idintID 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.databse_specification_id.
statement_line_numberintIf the class is a module, returns the line number on which the uncontained use is located. Otherwise the value is null.
statement_ offset_beginintIf 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_endintIf 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_typenvarchar(512)The type of statement.
feature_ namenvarchar(256)Returns the external name of the object.
feature_type_namenvarchar(256)Returns the type of feature.

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

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.

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;  

Contained Databases

Community Additions

ADD
Show:
© 2016 Microsoft