The application was supported by two databases. Each database had the need to reference objects in the other using a three part name.
When it came to to releasing the database to production I wanted to check that all references across databases were correct that is. That is objects in the databases didn’t access or reference the wrong databases.
I thought I’d make use of the sys.dm_db_uncontained_entities stored procedure to look for objects that span the database boudary.
I came up with the following query that did what I wanted.
SELECT DISTINCT SO.NAME ,so.object_id ,sm.DEFINITION ,UE.class_desc FROM sys.dm_db_uncontained_entities AS UE LEFT JOIN sys.objects AS SO ON UE.major_id = SO.object_id LEFT JOIN sys.sql_modules AS sm ON so.object_id = sm.object_id WHERE sm.DEFINITION IS NOT NULL AND sm.DEFINITION LIKE '%SearchString%'The above query can be modified to remove the uncontained entities reference to check for a string appearing in any object in the database.
SELECT DISTINCT so.NAME AS ObjectName ,so.type_desc ,sm.DEFINITION FROM sys.sql_modules sm INNER JOIN sys.objects so ON sm.object_id = so.object_id WHERE sm.DEFINITION LIKE '%SearchString%' ORDER BY so.type_desc ,so.NAMEIf we can help with a SQL Server problem feel free to check out our Consultancy Page or Contact Us