CODEWITHSUNDEEP
sqlsql-servert-sql
Mohammad Nadeem
Mohammad Nadeem

Reputation: 9392

xtype vs type column in Sysobjects

Can anyone tell me why Sysobjects table have two columns xtype and type. Both are of data type char(2) and stores the object type.

Upvotes: 1

Views: 6933

Answers (2)

Martin Smith
Martin Smith

Reputation: 453318

You can look at the definition of sysobjects

select OBJECT_DEFINITION(OBJECT_ID('sysobjects')) 
AS [processing-instruction(x)] FOR XML PATH('')

Which Gives

CREATE VIEW sys.sysobjects AS
 SELECT  
 /*... Snip ...*/
 xtype = type collate database_default,
 /*... Snip ...*/
 type = convert(char(2), case type
  when 'UQ' then 'K' when 'PK' then 'K'
  else type collate database_default end),
 /*... Snip ...*/  
 FROM sys.sysschobjs

So it appears they only differ in whether UQ and PK or shown as K or not.

Upvotes: 0

codingbadger
codingbadger

Reputation: 43984

I believe type should be used as it is still in the new view. xtype is there for backward compatibility.

NOTE:

It appears that sys.sysobjects will be removed in future versions and you should use sys.objects instead.

Upvotes: 3

Related Questions