Wednesday, 6 April 2016

TSQL– Search for Strings in Object Definitions

I have been  working nn an environment  that had multiple copies of a database on the same SQL server instance. In this case the SQL Server was acting as both a UAT and Training environment.
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.NAME
If we can help with a SQL Server problem feel free to check out our Consultancy Page or Contact Us

No comments:

Post a Comment

Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...