Hi,

Is there any difference in the result of these two queries?

select name from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
order by name
select name from sysobjects where sysstat & 0xf = 3 order by name

Thanks
Henrik.

sysstat is a column that is documented as "Internal status information", so
it is not really advisable to use. If I remember correctly  Enterprise
Manager generates scripts with sysstat & 0xf = 3 and Query Analyzer
generates them with OBJECTPROPERTY(id, N'IsUserTable') = 1.

You can also use:
select name from sysobjects where xtype = 'U' order by name

or my preferred one:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

Show quoteHide quote

Hi Jacco,

Thak you very much for your reply. I will change my code. What is your
opinion about this then:

select
sysobjects.name AS TableName,
sysindexes.name AS IndexName,
CASE WHEN (sysindexes.status & 2048) <> 0 THEN 1 ELSE 0 END AS PrimaryKey,
       case when (sysindexes.status & 4096) <>0 then 1 ELSE 0 end
UniqueConstraint,
index_col(sysobjects.name,sysindexes.indid, 1) AS IndexField1,
index_col(sysobjects.name,sysindexes.indid, 2) AS IndexField2,
index_col(sysobjects.name,sysindexes.indid, 3) AS IndexField3,
index_col(sysobjects.name,sysindexes.indid, 4) AS IndexField4,
index_col(sysobjects.name,sysindexes.indid, 5) AS IndexField5,
index_col(sysobjects.name,sysindexes.indid, 6) AS IndexField6,
index_col(sysobjects.name,sysindexes.indid, 7) AS IndexField7,
index_col(sysobjects.name,sysindexes.indid, 8) AS IndexField8,
index_col(sysobjects.name,sysindexes.indid, 9) AS IndexField9,
index_col(sysobjects.name,sysindexes.indid, 10) AS IndexField10
  from sysobjects, sysindexes, sysindexkeys
  where OBJECTPROPERTY(sysobjects.id, N'IsUserTable') = 1
  AND sysindexes.id = sysobjects.id
  and sysindexkeys.id = sysindexes.id
  and sysindexkeys.indid = sysindexes.indid
  AND sysindexes.indid > 0 and sysindexes.indid < 255
         AND (sysindexes.status & 2) <> 0
group by sysobjects.name, sysindexes.name, sysindexes.status,
sysindexes.indid


I use it to retrive index information.

Thanks
Henrik.

Show quoteHide quote

Case Statement?
View Explaination
Index Question
Warning: Null value is eliminated (causing sproc to fail)
I'm confused need some help ... with Union