Friday, January 2, 2015

Find all user-created objects in your system databases

System databases are for system objects.  Pretty simple.  If user objects are created within the system databases, then we also need to include these databases in our DR plans.  For this and countless other reasons, it really is not a good practice to allow user-defined objects within your SQL Server system databases.

In this post I've just given a couple of quick statements you can use to identify user created objects in any of your system databases.

/*  Use this to find any user-defined objects in the system databases, if exists.  
    As-is, it targets the master database, but works the same in any system db.
To target model or msdb, change the database name in the USE line.            */

USE master;  -- << use master, model or msdb
SELECT [ObjectName], [Schema/Owner],
o.type_desc [ObjectType]
sys.all_objects o INNER JOIN sys.schemas s
          ON o.schema_id = s.schema_id LEFT OUTER JOIN sys.database_principals po
            ON o.principal_id = po.principal_id LEFT OUTER JOIN sys.database_principals ps
              ON s.principal_id = ps.principal_ID
o.is_ms_shipped <> 1; -- 'is_ms_shipped' means it was shipped by MSFT on the cd.  
      -- If is_ms_shipped = 0, that means it is user-created.

In cases of upgraded databases, or database diagramming, I have heard that is_ms_shipped is not always 100% reliable.  I have never actually seen that, but to be safe, you could use this approach, which is a combination of is_ms_shipped and the 'sys' schema name.

SELECT * FROM sys.objects 
WHERE SCHEMA_NAME(schema_id) <> 'sys'  
AND is_ms_shipped = 0  
AND parent_object_id NOT IN (
SELECT object_id  
FROM sys.objects  
WHERE SCHEMA_NAME(schema_id) = 'sys'  
        OR is_ms_shipped = 1); 

These are additional references to the different system catalog views:

No comments:

Post a Comment