查看SQL Server数据库对象列表时如何识别系统对象？ [英] How to identify system objects when viewing list of SQL Server database objects?

问题描述

我正在尝试列出服务器上所有数据库中的所有存储过程，但似乎无法可靠地过滤掉系统对象。我正在使用：

I'm trying to list all the stored procedures from all the databases on my server, and I can't seem to filter out system objects reliably. I was using:

SELECT *
  FROM sysobjects
 WHERE id > 100

除了MSDB之外，这似乎在每个数据库中都能正常工作具有正常外观的ID，但它们是系统存储的proc。据我所知，我无法使用sysobjects表中的任何值来过滤掉系统存储的procs-其他人是否知道可以用于过滤的值？

Which seems to work fine in every database except MSDB, which is full of a ton of stored procs with normal-looking IDs, but they're system stored procs. As far as I can tell, there's no way for me to filter out system stored procs using any of the values in the sysobjects table - does anybody else know of a value that can be used to filter?

它们都被标记为type = P，这意味着它是一个存储的proc，但是似乎没有标志来指定它是系统存储的proc还是用户的proc。我可以将sys.objects视图和过滤器用于 IsMsShipped = 0，但是我希望某些东西也可以在SQL 2000上使用，因此，如果可能的话，我希望使用较旧的视图（例如sysobjects）。

They're all marked as type="P", which means it's a stored proc, but there seems to be no flag to specify if it's a system stored proc or a user one. I can use the sys.objects view and filter for "IsMsShipped=0", but I'd like something that also works on SQL 2000, so I'd prefer to use the older views (like sysobjects) if it's possible.

推荐答案

这适用于我的SQL Server 2008 R2安装。除了用户数据库，我什么都看不到

This works on my SQL Server 2008 R2 install. I don't see much at all except for user databases

SELECT 
    *
FROM
   sys.objects
WHERE
   OBJECTPROPERTY(object_id, 'IsMSShipped') = 0

您可以将 sys.objects 更改为sys.tables和它仍然有效，或使用类型列进行过滤。或使用OBJECTPROPERTY（object_id，'IsProcedure'）等。

You can change sys.objects to say, sys.tables and it still works, or use the "type" column to filter. Or use OBJECTPROPERTY(object_id, 'IsProcedure') etc.

注意：它是SQL Server 2005+中的sys.objects。

Note: it's sys.objects in SQL Server 2005+

注释2： OBJECTPROPERTY 将起作用对于SQL Server 2000也是如此：

Note 2: OBJECTPROPERTY will work for SQL Server 2000 too:

SELECT 
    *
FROM
   sysobjects
WHERE
   OBJECTPROPERTY(id, 'IsMSShipped') = 0

这篇关于查看SQL Server数据库对象列表时如何识别系统对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！