Upgrading old query 7 to 2005.

#1

Hi all;
To Defrag the index at the moment we use a query that among many other
things it does:
...
declare object_cursor cursor for
select so.name, si.name
from sysindexes si, sysobjects so
where si.id=so.id
and so.xtype='U'
and si.indid>0
and si.indid<255
and si.status Not In (96, 2097248, 8388704, 10485856)
order by so.name, si.name
...
We are ussing a Sql-Server 2005 in compatibility mode 80

Trying to upgrade that query so it wont use the sysindexes therefore
we can´t use the status found that people recomends to use:

select * from sys.indexes where is_hypothetical = 1

But all this stadistic are not shown in this query e.g.

_WA_Sys_.....
etc....

Any suggestion how to handle this???

#2

valigula (valigula (AT) gmail (DOT) com) writes:

Quote:

Hi all;
To Defrag the index at the moment we use a query that among many other
things it does:
...
declare object_cursor cursor for
select so.name, si.name
from sysindexes si, sysobjects so
where si.id=so.id
and so.xtype='U'
and si.indid>0
and si.indid<255
and si.status Not In (96, 2097248, 8388704, 10485856)
order by so.name, si.name
...
We are ussing a Sql-Server 2005 in compatibility mode 80

Trying to upgrade that query so it wont use the sysindexes therefore
we can´t use the status found that people recomends to use:

select * from sys.indexes where is_hypothetical = 1

But all this stadistic are not shown in this query e.g.

_WA_Sys_.....
etc....

Use sys.indexes and sys.objects rather than sysindexes and sysobjects:

SELECT o.name, i.name
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
WHERE o.type = 'U'
AND i.is_hypothetical = 0
AND i.index_id > 0

In SQL 2005, the old system tables have been replace by catalog views.
The old system tables are now compatibility views, and you do best to
migrate to the new views instead. Books Online has the details.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

On 15 feb, 23:48, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

valigula(valig... (AT) gmail (DOT) com) writes:
Hi all;
To Defrag the index at the moment we use a query that among many other
things it does:
...
declare object_cursor cursor for
select so.name, si.name
from sysindexes si, sysobjects so
where si.id=so.id
and so.xtype='U'
and si.indid>0
and si.indid<255
and si.status Not In (96, 2097248, 8388704, 10485856)
order by so.name, si.name
...
We are ussing a Sql-Server 2005 in compatibility mode 80

Trying to upgrade that query so it wont use the sysindexes therefore
we can´t use the status found that people recomends to use:

select * from sys.indexes where is_hypothetical = 1

But all this stadistic are not shown in this query e.g.

_WA_Sys_.....
etc....

Use sys.indexes and sys.objects rather than sysindexes and sysobjects:

SELECT o.name, i.name
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
WHERE o.type = 'U'
AND i.is_hypothetical = 0
AND i.index_id > 0

In SQL 2005, the old system tables have been replace by catalog views.
The old system tables are now compatibility views, and you do best to
migrate to the new views instead. Books Online has the details.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks Erland,

but when i run the query "select * from sys.indexes where
is_hypothetical = 1" it does not return all the values that they do
when i query the sysindexes, can be that because the compatibility is
set to 80 ?

#4

valigula (valigula (AT) gmail (DOT) com) writes:

Quote:

but when i run the query "select * from sys.indexes where
is_hypothetical = 1" it does not return all the values that they do
when i query the sysindexes, can be that because the compatibility is
set to 80 ?

I'm not really sure what you mean, but keep in mind that in indexes and
statistics co-habits in sysindexes, whereas sys.indexes only have indexes,
and statistics are in sys.stats. Could this be the explanation?

Compatibility mode has nothing to do with it anyway.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Upgrading old query 7 to 2005.

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Upgrading old query 7 to 2005. - 02-15-2008 , 08:37 AM

Re: Upgrading old query 7 to 2005. - 02-15-2008 , 04:48 PM

Re: Upgrading old query 7 to 2005. - 02-16-2008 , 06:38 PM

Re: Upgrading old query 7 to 2005. - 02-17-2008 , 04:23 AM