Unlike some of the other relational databases i.e. Oracle and DB/2, SQL Server and Sybase programmable universe stretches beyond individual database containers into databases that share the same SQL Instance.
In essence, database programmable objects such as a Stored Procedures, views, functions, and triggers can reference objects in other databases.
Starting with SQL Server 2012, Microsoft introduces the concept of Contained databases.
Here is what MS documentation says about the rationale:
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server helps user to isolate their database from the instance in these ways:
- Much of the metadata that describes a database is maintained in the database. In addition to, or instead of, maintaining metadata in the master database
- All metadata are defined using the same collation.
- User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
- The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.
Contained Databases – Benefits
Programming towards full database containment offers benefits such as:
- Easier to move such databases from one instance to another
- Easier to move from one hosts to another during fail-over knowing that there is less dependency on the actual host and other residing databases
- Localize management role
Identify Objects that are not fully contained
Knowing the potential benefits of full containment, let us see how we can determine which objects have dependencies on other objects outsize their database.
As always, Microsoft’s has come to other aid.
There are a set of tools:
- Dynamic Management Views
- Uncontained Entities
- sys.dm_db_uncontained_entities ( This view shows any entities in the database that have the potential to be uncontained, such as those that cross-the database boundary. This includes those user entities that may use objects outside the database model. )
- Code Definition
- sys.sql_modules ( This views contains the programmable object’s code definition )
- sys.default_constraints ( Default Constraint code definition )
- Uncontained Entities
Pasted below is a set of SQL code.
The code rests fairly heavily on a splitter code. The splitter code separates out the module entry for each object into separates lines. There are various such code on the Net.
Here are some examples:
- Joining to a table function in SQL Server
- Erland Sommarskog (SQL Server MVP ) – Arrays in SQL Server 2005
- Aaron Bertrand – Split Strings – dbo.SplitStrings_CTE
For this exercise, we chose to use the Table Value Function from anvil-of-time.com
The first code, Code-1, queries the aforementioned views and lists un-contained entities. But, unfortunately it sometime breaks with the error pasted below:
Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
The second code, Code-2, relies on a loop and this seems to protect it from the brittleness of Code-1.
set nocount on; declare @newline nvarchar(30) set @newline = char(13) + char(10) select tblUCE.class , tblUCE.class_desc , tblUCE.major_id as objectID , case when (tblUCE.class = 1) then object_schema_name(tblUCE.major_id) + '.' + object_name(tblUCE.major_id) else cast(null as sysname) end as [entity] , tblUCE.statement_line_number , len(tblSM.[definition]) as lengthOFDefinition , tblSM.[definition] , tblSQLText.[Item] as sqlLine from sys.dm_db_uncontained_entities tblUCE inner join sys.sql_modules tblSM on tblUCE.major_id = tblSM.object_id inner join sys.objects tblO on tblSM.object_id = tblO.object_id cross apply [dbo].[uft_splitnotes](tblSM.[definition], @newLine) tblSQLText -- match line number and sql text seq no where tblUCE.statement_line_number = tblSQLText.SeqNo --filter out encryped objects and tblSM.[definition] is not null --filter out Microsoft shipped\owned objects and tblO.is_ms_shipped = 0 order by case when (tblUCE.class = 1) then object_schema_name(tblUCE.major_id) + '.' + object_name(tblUCE.major_id) else cast(null as sysname) end asc , tblUCE.statement_line_number asc
set nocount on; declare @newLine nvarchar(30) set @newline = nchar(13) + nchar(10) declare @tblCache TABLE ( [id] int not null identity(1,1) , [class] int not null , [classDescription] sysname , [objectID] int not null , [objectName] sysname null , statementLineNumber int null , [definition] nvarchar(max) null , [definitionLength] int null , [SQLLine] nvarchar(4000) null ) declare @id int declare @idMax int declare @definition nvarchar(max) declare @statementLineNumber int declare @sqlLine nvarchar(4000) set @id = 1 insert into @tblCache ( [class] , [classDescription] , [objectID] , [objectName] , statementLineNumber , [definition] , [definitionLength] ) select tblUCE.class , tblUCE.class_desc , tblUCE.major_id as objectID , case when (tblUCE.class = 1) then object_schema_name(tblUCE.major_id) + '.' + object_name(tblUCE.major_id) else cast(null as sysname) end as [entity] , tblUCE.statement_line_number , tblSM.[definition] , len(tblSM.[definition]) from sys.dm_db_uncontained_entities tblUCE left outer join sys.sql_modules tblSM on tblUCE.major_id = tblSM.object_id --http://msdn.microsoft.com/en-us/library/ff929336.aspx --1 = Object or column (includes modules, XPs, views, synonyms, and tables). and tblUCE.class = 1 --filter out encryped objects and tblSM.[definition] is not null left outer join sys.objects tblO on tblSM.object_id = tblO.object_id --filter out Microsoft shipped\owned objects and tblO.is_ms_shipped = 0 set @idMax= @@rowcount set @id =1 while (@id <= @idMax) begin /* Get Definition and "offending SQL Line Number */ select @definition = tblC.[definition] , @statementLineNumber = tblC.statementLineNumber from @tblCache tblC where [id] = @id /* Get SQL Line */ select @sqlLine = tblSQLText.Item from [dbo].[uft_splitnotes](@definition, @newLine) tblSQLText where tblSQLText.SeqNo = @statementLineNumber update tblC set tblC.SQLLine = @sqlLine from @tblCache tblC where tblC.[id] = @id set @id = @id + 1 end -- while select tblC.* from @tblCache tblC order by tblC.objectName , tblC.statementLineNumber go
This morning I opened up a Microsoft Connect entry (#1053921) to help track and solicit community feedback per the bug I aforementioned.
- Receiving error “A severe error occurred on the current command” on accessing sys.sql_modules
- Posted a follow-up post at Microsoft – SQL Server – Identify “uncontained” objects — Using SQLCLR to split strings ( https://danieladeniji.wordpress.com/2014/12/16/32846/ ). Interestingly enough we are able to avoid the problem by using Adam Mechanic’s SQLCLR function.
- Via email, Erland Sommarskog responded back to my email and confirmed that he is able to reproduce the problem. Like I always say there is no better community in the world.
2016-march 25th to March 29th
Microsoft’s Sergey Ten worked and resolved issue.
Like that guy who will give his right arm to be an ambidextrous, I would do same to be part of SQL community.