Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sysstat & 0xf - what and why? Expand / Collapse
Author
Message
Posted Tuesday, November 25, 2008 12:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:46 AM
Points: 365, Visits: 695
I was supplied a script to run that contains the following:

if exists (select * from sysobjects where id = object_id('dbo.mytbl') and sysstat & 0xf = 3)
drop table dbo.mytbl
GO

In BOL, there's no real explanation of sysstat other than "Reserved for SQL Server internal use only. Future compatibility is not guaranteed."

When I googled it, I see quite a bit of sysstat & 0xf = 4 (not 3).
What is this? Is it really necessary to use it the way it is used above?
Post #608637
Posted Wednesday, November 26, 2008 7:58 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:26 AM
Points: 4,427, Visits: 4,173
The values in sysstat refer to what type of object it is. 3 means it's a user table. But I would advise against using this column in your scripts, because first of all in SQL 2005 you should work with sys. objects which doesn't have a sysstat column and second Microsoft can change the values in a future version.

The proper way of checking it is a user table would be using the OBJECTPROPERTY function.

IF EXISTS (SELECT * FROM sys.objects WHERE name ='mytbl' 
AND OBJECTPROPERTY(OBJECT_ID('dbo.mytbl'),'IsUserTable')=1)
DROP TABLE dbo.mytbl



Markus Bohse
Post #609111
Posted Wednesday, November 26, 2008 8:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:46 AM
Points: 365, Visits: 695
Thanks for the info. I'm used to working in 2000 and usually just check sysobjects where xtype = 'u' . I see where in 2005, the col is type instead of xtype. Will pass along to others the ObjectProperty(isusertable).
Post #609149
Posted Thursday, June 19, 2014 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 1:46 AM
Points: 1, Visits: 2
Actually "&" used here means Bitwise.
binary value of 0xf is 0000 1111
Example:
Bitwise A & B
A = 0000 1111
B = 0000 0011
-------------------
then: 0000 0011

"sysstat & 0xf =3" will guarantee that sysstat value is 3.

See MSDN site: http://msdn.microsoft.com/en-us/library/ms174965.aspx

Hope this can help!
Post #1583765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse