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
o.name [ObjectName],
s.name [Schema/Owner],
o.type_desc [ObjectType]
FROM
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
WHERE
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:
sys.database_principals http://msdn.microsoft.com/en-us/library/ms187328.aspx
No comments:
Post a Comment