What Reaches Outside Your Database? sys.dm_db_uncontained_entities
Every database likes to pretend it is self-contained. Then you restore it onto another instance and discover the stored procedure that queries a sister database, the synonym pointing at a linked server, the login-mapped user that no longer maps to anything, and the job of untangling begins at the worst possible time: after the move.
SQL Server can tell you all of this before the move. The DMV is sys.dm_db_uncontained_entities[1], it has been shipping since SQL Server 2012, and almost nobody uses it, partly because its raw output is IDs and offsets rather than answers. This post fixes that.
I wrote recently about checking Enterprise-only feature usage before downgrading to Standard Edition. This is the companion question. Where that post asks “can this database physically run over there?”, this one asks “will everything inside it still work when it gets there?”
What the DMV knows
sys.dm_db_uncontained_entities was built for the contained databases feature[2], and it lists everything in the current database that breaches containment: objects whose behavior depends on something outside the database boundary. You do not need to be using (or even planning to use) contained databases for it to be useful. “Depends on something outside the database” is exactly the checklist you want for a migration, a restore to a new environment, a dev-environment refresh, or handing a database to another team.
What it flags includes:
- Modules (procedures, functions, triggers, views) that use three- or four-part names to reference other databases or linked servers
- Synonyms whose base object lives in another database or on another server
- Dynamic SQL, which might reach anywhere (the DMV cannot see inside the string, so it flags the usage)
- Users mapped to instance-level logins (Windows or SQL logins), as opposed to contained users
- Service Broker routes, full-text usage, and other instance-coupled features
The raw output, though, is class_desc, major_id, and statement offsets. Turning that into names and code fragments takes a little work.
A query that gives you answers
This resolves each entity to its name and type, and for modules it extracts the exact fragment of code that breached containment, using the statement offsets against sys.all_sql_modules:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
USE [your_database]; DECLARE @exclude_qualified_names bit = 1; DECLARE @exclude_dynamic_sql bit = 1; DECLARE @exclude_system_feature_usage bit = 1; SELECT [ddue].[class_desc] , [ddue].[statement_type] , [ddue].[feature_name] , [ddue].[feature_type_name] , [item_name] = COALESCE ( QUOTENAME([sc].[name]) + N'.' + QUOTENAME([o].[name]) , [dp].[name] , [r].[name] , OBJECT_NAME([fti].[object_id]) ) , [item_type] = COALESCE ( [o].[type_desc] , [dp].[type_desc] , CASE WHEN [r].[route_id] IS NOT NULL THEN N'ROUTE' ELSE NULL END , CASE WHEN [fti].[object_id] IS NOT NULL THEN N'Full Text Index' ELSE NULL END ) , [fragment_text] = COALESCE ( /* the offending fragment inside the module definition */ SUBSTRING ( [asm].[definition] , ([ddue].[statement_offset_begin] / 2) , ([ddue].[statement_offset_end] / 2) - ([ddue].[statement_offset_begin] / 2) ) /* or, for a synonym, its target */ , [s].[base_object_name] ) FROM [sys].[dm_db_uncontained_entities] AS [ddue] LEFT JOIN [sys].[objects] AS [o] ON [ddue].[major_id] = [o].[object_id] AND [ddue].[class_desc] = N'OBJECT_OR_COLUMN' LEFT JOIN [sys].[schemas] AS [sc] ON [o].[schema_id] = [sc].[schema_id] LEFT JOIN [sys].[all_sql_modules] AS [asm] ON [ddue].[class_desc] = N'OBJECT_OR_COLUMN' AND [ddue].[major_id] = [asm].[object_id] LEFT JOIN [sys].[synonyms] AS [s] ON [ddue].[class_desc] = N'OBJECT_OR_COLUMN' AND [o].[type_desc] = N'SYNONYM' AND [ddue].[major_id] = [s].[object_id] LEFT JOIN [sys].[database_principals] AS [dp] ON [ddue].[class_desc] = N'DATABASE_PRINCIPAL' AND [ddue].[major_id] = [dp].[principal_id] LEFT JOIN [sys].[routes] AS [r] ON [ddue].[class_desc] = N'ROUTE' AND [ddue].[major_id] = [r].[route_id] LEFT JOIN [sys].[fulltext_indexes] AS [fti] ON [ddue].[class_desc] = N'INDEX' AND [ddue].[major_id] = [fti].[object_id] WHERE ( @exclude_qualified_names = 0 OR [ddue].[feature_name] <> N'Server or Database Qualified Name' ) AND ( @exclude_dynamic_sql = 0 OR [ddue].[feature_name] <> N'Dynamic SQL' ) AND ( @exclude_system_feature_usage = 0 OR [ddue].[feature_type_name] NOT LIKE N'System %' ) ORDER BY [ddue].[class_desc] , [ddue].[major_id]; |
The fragment_text column is the part that earns its keep. Instead of “procedure X breaches containment,” you get the actual [OtherDatabase].[dbo].[SomeTable] reference, character for character, ready to paste into a migration ticket.
About those three filter switches
The DMV is thorough to a fault, so the query ships with three noise filters, each defaulting to on. Flip them to 0 when you want the full picture:
- @exclude_qualified_names – a module that references
[MyOwnDatabase].[dbo].[MyTable], fully qualified with its own database name, is technically uncontained (the name breaks if the database is restored under a different name) but usually harmless. These rows dominate the output in most real databases, which is why the filter defaults to on. Set it to 0 before a restore-under-a-new-name, because that is exactly when self-references bite. - @exclude_dynamic_sql – every module containing
EXECorsp_executesqlon a string gets flagged, because the DMV cannot prove the string stays inside the database. Useful for an audit, noisy for a first pass. - @exclude_system_feature_usage – filters rows whose
feature_type_namestarts with “System”, which mostly report system procedure usage rather than dependencies you can act on.
Reading the results like a migration checklist
In practice the rows sort themselves into three buckets:
- Things that will break loudly: cross-database queries and synonyms pointing at databases that will not exist on the target instance, and linked-server references. These need a plan before the move: bring the other database along, re-point the synonym, or rewrite the module.
- Things that will break quietly: users mapped to logins. The restore succeeds, the application connects as a different principal, and three days later someone cannot run month-end because their user is orphaned. The
DATABASE_PRINCIPALrows are your pre-flight list for login re-mapping. - Things that need eyeballs: the dynamic SQL rows. No tool can tell you whether the string built at runtime reaches outside the database; someone has to read them. The fragment text at least takes you straight to the right line.
One honest limitation: like every dependency DMV, this reflects what can be resolved from metadata at the time you ask. It cannot see references constructed at runtime, and deferred name resolution means a procedure referencing a database that does not currently exist may not report everything you would hope. Run it on the real database, not an empty schema copy, and treat “zero rows” as “nothing detectable” rather than a guarantee.
The same whole-instance sweep pattern I used for upgrading compatibility levels across an instance applies here too: wrap the query in a loop across every database and you have an instance-wide external-dependency inventory, which is a genuinely pleasant thing to already have when the migration project kicks off.
Has an uncontained dependency ever ambushed one of your migrations? Tell me about it in the comments, or find me on Bluesky or LinkedIn.
References
- sys.dm_db_uncontained_entities (Transact-SQL) – Microsoft Learn. Column reference and the entity classes the DMV reports. ↩
- Contained databases – Microsoft Learn. The containment model this DMV was built to support, including what counts as a containment breach. ↩