Script of the day – Finding Foreign Objects in master


Have you ever forgot to set the database when you ran a query, filling the master system database with your user objects? And I’ll bet you used the dbo user too, didn’t you? Doh!


Here’s a quick way to ferret those out so you can clean them up:


/* Find foreign objects in the master database
Buck Woody – 2007 (with thanks to Jaco Schalkwijk)
Note: help_text is a Microsoft Object even though it returns
with this script.
*/


USE master
GO


–SQL 2005:
SELECT name
FROM
sys.objects
WHERE OBJECTPROPERTY([object_id], ‘IsMSShipped’) = 0;
GO


— SQL 2000:
SELECT name
FROM
sysobjects
WHERE OBJECTPROPERTY([id], ‘IsMSShipped’) = 0
GO

Skip to main content