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

Solution 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 whereOBJECTPROPERTY(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.

Solution 2:

Try something like:

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

Solution 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;