Export (0) Print
Expand All

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.

Applies to: SQL Server (SQL Server 2012 through current version).

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.

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.

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;

Community Additions

ADD
Show:
© 2014 Microsoft