2

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Randy Minder
  • 47,200
  • 49
  • 204
  • 358
  • 1
    You may want to check out this SO Q/A: http://stackoverflow.com/questions/2910077/what-is-syncobj-in-sql-server – bzarah Jun 29 '11 at 13:44
  • What version of SQL Server are you using? This can affect the validity or quality of the responses you get. There are better answers than sysobjects if you are on 2005+, for example. Suggesting tagging with the exact (or minimum) version (e.g. tag "sql-server-2008"). – Aaron Bertrand Jun 29 '11 at 13:51
  • @Aaron - what better options are there than sysobjects if I want a complete list of all objects in my database? I understand I can use the information schema views, but then I would need to query a dozen of them. Using 2008. – Randy Minder Jun 29 '11 at 15:00
  • sys.objects is superior to sysobjects. The latter is a compatibility view that is still in the product only because of the amount of code out there that would have to change. You should use catalog views, not compatibility views, going forward. – Aaron Bertrand Jun 29 '11 at 15:50

3 Answers3

2

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.

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

Try something like:

select *
from sysobjects
where name NOT IN (
    select name from sys.system_views
)
Duncan Howe
  • 2,965
  • 19
  • 18
  • 1
    Probably better to use the object_id actually. `select * from sysobjects where id NOT IN ( select object_id from sys.system_views )` – Duncan Howe Jun 29 '11 at 13:44
  • remember to join your tables. select * from sysobjects where id NOT IN ( select object_id from sys.system_views where sysobjects.id = object_id) or better yet, use exists. – t-clausen.dk Jun 29 '11 at 13:59
  • This still allows system tables and other objects through. – Aaron Bertrand Jun 29 '11 at 15:54
  • @t-clausen.dk while admittedly a best practice, and while I do prefer EXISTS in a lot of cases, the reference is only really required when the value returned by NOT IN can be NULL. Since sys.system_views.object_id is not NULLable, I can't imagine a scenario where the non-correlated subquery will produce an invalid result. – Aaron Bertrand Jun 29 '11 at 15:59
0

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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Note that this will still return some system objects, e.g. the dtproperties table (added if you add diagram support to a database) is not considered MS-shipped but rather a user table. – Aaron Bertrand Jun 29 '11 at 15:54