SQL Server Health Check

Finding User Tables in SQL Server System Databases

Updated October 29, 20252 min read

Find user tables in system databases. A small wooden table and chair stand out of place between rows of server cabinets.

Why should you care about user data tables?

User data tables should not be in the system databases (master, msdb):

  1. In case of failure, there is a high chance that those tables will be lost.
  2. In the case of migration, these tables may be forgotten and not restored.
  3. It shows that some users may have excessive access rights to system databases.
  4. Also, maybe someone is running a query or pointing a third-party tool at the wrong database, deploying some objects, and not cleaning up afterwards.

How can you find user-created tables in the SQL Server system databases?

You can use the script below to list all user tables in master and msdb databases.

T-SQL
SELECT 'msdb' AS SysDatabaseName
	,name AS UserTable FROM    msdb.sys.tables WHERE   is_ms_shipped = 0
	AND name NOT LIKE '%DTA_%'

UNION
	
	SELECT  'model' AS SysDatabaseName
	,name AS UserTable FROM    model.sys.tables WHERE   is_ms_shipped = 0;

How to fix them?

  1. Rename the tables.
  2. If there are no problems after X (30?) days, delete them.
  3. It could also fix security, so this can’t happen again.
  4. Ideally, you want to add automated DDL (any object modifications) monitoring to log all.

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜