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 –


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)?

Fig #1


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,, * 
 --, left(,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 NOT IN ('sys', 'INFORMATION_SCHEMA')
         and = 'dbo'
         and left(,3) NOT IN ('sys', 'spt')
         and left(,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 –

Fig #2


This blog is partially based on the following resources: