sysobjects.sysstat

3 Nov 2005 5:30 PM

len

Does anybody know what the sysobjects.sysstat field is used for and when/if
it should be used in programming? It's just that I've found it referenced in
some old generated SQL scripts and used in logic (if object doesn not exist
then create....) and was thinking I should replace it. The actual systax I
have is when the script goes about creating a table -only if said table does
not already exist- is as follows:

if not exists (select * from sysobjects where id = object_id('MyTableName')
and sysstat & 0xf = 3)

3 Nov 2005 5:43 PM

Aaron Bertrand [SQL Server MVP]

> then create....) and was thinking I should replace it. The actual systax I
> have is when the script goes about creating a table -only if said table
> does
> not already exist- is as follows:
>
> if not exists (select * from sysobjects where id =
> object_id('MyTableName')
> and sysstat & 0xf = 3)

Personally, I don't think this should be used. For one reason, sysobjects
will eventually go away (there is a whole slew of new management objects in
SQL Server 2005, e.g. sys.tables, which are designed to replace the old
system tables architecture). And the sysstat column is marked for internal
use, so its meaning can change between releases or even between service
packs or with a hotfix. So, using it in your code is probably not a good
idea, and will break your code sooner or later.

This should do the job equally well:

IF OBJECTPROPERTY(OBJECT_ID('dbo.MyTableName'), 'IsUserTable')) = 1
BEGIN
    -- returns NULL if the table doesn't exist
    -- returns 0 if the table is a system table
    DROP TABLE dbo.MyTableName
    -- CREATE TABLE dbo.MyTableName
END

Let the system figure out where to look up the metadata. Don't touch system
tables if you don't have to, and certainly don't rely on columns marked for
internal use.