Introduction
I’m going to assume that you already know how to block user objects from being created in a master database (see my previous blog here – https://datasteve.com/2020/10/23/block-user-objects-from-being-created-in-a-master-database/.
Problem
I have a trigger now that prevents new user objects from being created, but what how do I discover any existing objects, i.e. objects that are probably created by mistake before the trigger (Fig #1)?
Solution
The solution is fairly simple. You run a query against all the objects in a master database while excluding all the known system objects. The following piece of code is going to uncover all the user objects that are currently in a master database.
select smas.name, objs.name, *
--, left(smas.name,3)
from master.sys.objects as objs
inner join master.sys.schemas as smas on objs.schema_id = objs.schema_id
where 1=1
--and smas.name NOT IN ('sys', 'INFORMATION_SCHEMA')
and smas.name = 'dbo'
and left(objs.name,3) NOT IN ('sys', 'spt')
and left(objs.name,5) NOT IN ('sp_MS', 'sp_ss', 'MSrep')
and objs.type_desc NOT IN ('INTERNAL_TABLE','SERVICE_QUEUE')Running that code on my machine will bring back a user stored procedure that I created by mistake long time ago (Fig #2) for my first ever blog post – https://datasteve.com/2019/09/11/verify-your-database-backups/.
Disclaimer
This blog is partially based on the following resources:
DataSteve 