SQL Server - Querying sysobjects

问题:

I notice that when I query dbo.sysobjects, to determine all the objects in my database, it also picks up all system views whose name starts with 'syncobj_'. These have an xtype of 'V' and there doesn't appear to be any way I can know these are system views, and not my own, except by examining the name of the view. Is there some other way? I would like to exclude these from a query I'm in the process of creating.

回答1:

See OBJECTPROPERTY:

IsMSShipped

Any schema-scoped object

Object created during installation of SQL Server. 1 = True 0 = False

Use it something like:

SELECT * from sysobjects where OBJECTPROPERTY(ID,N'IsMSShipped') = 0

It's documentation is a bit off though - it also assists you with excluding other objects added "by" SQL Server at a later date also - e.g. any replication related objects are also considered to be IsMSShipped.

回答2:

Try something like:

select *
from sysobjects
where name NOT IN (
    select name from sys.system_views
)

回答3:

Since you are using SQL Server 2008, there is very little reason to continue using the outdated compatibility view sysobjects. You should instead use the catalog view sys.objects, introduced in SQL Server 2005. As an added bonus, you don't need to call an external OBJECTPROPERTY() function for every single row, since it contains a column called is_ms_shipped that reveals the same information.

SELECT OBJECT_SCHEMA_NAME([object_id]), name, type
 FROM sys.objects
 WHERE is_ms_shipped = 0;