User objects in the master database
Recently when doing a database upgrade for a client, I was informed that we had missed some user procedures, tables and functions in the master database. I needed a way to track all these down so that I could confirm that all the same user objects existed in the new master database, as those on the old server’s master database. Since this was a clean install to a fresh machine, I did no want to just move the master, msdb and model databases over.
Here is the query that I ended up using to track down the user objects in the master database.
SELECT o.NAME AS ObjectName
,o.type_desc AS ObjectType
,SUM(st.row_count) AS Rows
,CAST(SUM(st.used_page_count) / 128.0 AS DECIMAL(36, 1)) AS SizeInMB
FROM master.sys.objects o
LEFT JOIN master.sys.dm_db_partition_stats st ON st.object_id = o.object_id AND st.index_id < 2
GROUP BY o.object_id
,o.NAME
,o.type_desc
,o.is_ms_shipped
HAVING o.is_ms_shipped = 0
AND o.NAME <> 'sp_ssis_startup'
AND o.type_desc NOT LIKE '%CONSTRAINT%';
If you come across a similar situation and need to find user objects in the master database then perhaps this query will work well for you also.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Steve, in my case, I have a large number of procedures that I’ve created in the master database, which are marked as system objects via sp_msMarkSystemObject; that allows them to leverage relative paths to sys.objects, etc, so it varies on database context.
so in my case, i ignore everything that is in the sys schema, instead of using is_ms_shipped = 0
–#################################################################################################
— 2020-08-09 08:39:31.331 MachineName:STORM-ORIGIN; ComputerBiosName:STORM-ORIGIN;
— STORM-ORIGIN\Lowell STORM-ORIGIN\Lowell Authorization Scheme: NTLM STORM-ORIGIN
— Context: STORM-ORIGIN; Database:master; Version: [SQL 2016 || 13.0.4259.0] Developer Edition (64-bit)
–#################################################################################################
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
SELECT
schema_name(schema_id) As SchemaName,
o.NAME AS ObjectName
,o.type_desc AS ObjectType
,SUM(st.row_count) AS Rows
,CAST(SUM(st.used_page_count) / 128.0 AS DECIMAL(36, 1)) AS SizeInMB
FROM master.sys.objects o
LEFT JOIN master.sys.dm_db_partition_stats st ON st.object_id = o.object_id AND st.index_id < 2
GROUP BY o.object_id
,schema_name(schema_id)
,o.NAME
,o.type_desc
,o.is_ms_shipped
HAVING schema_name(schema_id) ‘sys’
AND o.NAME ‘sp_ssis_startup’
AND o.type_desc NOT LIKE ‘%CONSTRAINT%’;
Thanks Lowell for the input. I had not thought about that option, but I like it.
-Steve Stedman