How do we differentiate between USer tables and System Tables in SQL Server

• ANIL MAHADEV

  Old Hand

  Points: 345

  More actions

  May 28, 2004 at 6:43 am

  #84585

  How do we differentiate between USer tables and System Tables in SQL Server

  Thanks

  Plz Reply ASAP

  Best Regards

  Anil Mahadev

  Software Dev 2 Da Max

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 28, 2004 at 3:34 pm

  #508302

  How do you mean. There are many ways tables can be viewed and each has a specific way. FOr instanes if you are querying sysobjects in a db then use

   

  select * from sysobjects where xtype = 'u' and status > 0

• hoo-t

  SSChampion

  Points: 12713

  More actions

  June 2, 2004 at 3:23 pm

  #508735

  To expand ever so slightly on Antares686's response -

  In sysobjects,

  xtype = 'u' is a user table

  xtype = 's' is a system table

  and sysobjects is a system table in each database that contains information about all of the "objects" in the database.

  Steve

• Antares686

  SSC Guru

  Points: 125444

  More actions

  June 3, 2004 at 6:18 am

  #508808

  Just and additional comment.

  The

  status > 0

  is added to cover items like dtproperties which is a system table but marked as a user table. All system objects have a bitmask applied that makes status a negative number which covers dtproperties as well.

• hoo-t

  SSChampion

  Points: 12713

  More actions

  June 3, 2004 at 9:56 am

  #508854

  Antares686,

  I did not know that, thank you!!!

  Steve