Drop Index on System Tables

SQL SERVER 2000

System let's you alter the system tables and add indexes. However, it won't
let you drop the index afterward.

Anybody know how to drop an index on a system table?

Thanks,

Kevin"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> SQL SERVER 2000
> System let's you alter the system tables and add indexes. However, it
> won't
> let you drop the index afterward.
> Anybody know how to drop an index on a system table?
> Thanks,
> Kevin

You don't say which table or what error you get, but in any case Microsoft
does not support any modifications to system tables, so the best option is
probably to restore from a backup. If you can post more detailed
information, then someone may have a better suggestion, but in general you
shouldn't touch system tables at all.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411dc8f8$1_2@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> > SQL SERVER 2000
> > System let's you alter the system tables and add indexes. However, it
> > won't
> > let you drop the index afterward.
> > Anybody know how to drop an index on a system table?
> > Thanks,
> > Kevin
> You don't say which table or what error you get, but in any case Microsoft
> does not support any modifications to system tables, so the best option is
> probably to restore from a backup. If you can post more detailed
> information, then someone may have a better suggestion, but in general you
> shouldn't touch system tables at all.

And further more, with some of the tables, it may appear you've succeeded,
but in reality nothing has changed, or upon a restart things were the way
they started. sysjobs I believe is one such derived table.

> Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411dc8f8$1_2@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:3sgTc.6297$Ux.622@.newssvr29.news.prodigy.com. ..
> > SQL SERVER 2000
> > System let's you alter the system tables and add indexes. However, it
> > won't
> > let you drop the index afterward.
> > Anybody know how to drop an index on a system table?
> > Thanks,
> > Kevin
> You don't say which table or what error you get, but in any case Microsoft
> does not support any modifications to system tables, so the best option is
> probably to restore from a backup. If you can post more detailed
> information, then someone may have a better suggestion, but in general you
> shouldn't touch system tables at all.
> Simon
>
I have an ERP application which constantly scans the sysobjects table for
xtype ''U'. I was attempting to speed up this particular SELECT statement
by adding an index to the xtype column (which seemed like a good idea at the
time). I ran a DBCC CHECKDB and it threw msg's 8951 and 8955. I actually
came across the problem when I tried to apply the script referenced in KB
293177 when it failed to complete.

I'm currently working on restoring the database into a new schema to resolve
the issue.

Kevin|||Kevin Haugen (khaugen@.pacbell.net) writes:
> I have an ERP application which constantly scans the sysobjects table
> for xtype ''U'. I was attempting to speed up this particular SELECT
> statement by adding an index to the xtype column (which seemed like a
> good idea at the time). I ran a DBCC CHECKDB and it threw msg's 8951
> and 8955.

It is dubious that even if sysobjects would be a normal table that this
would be a good thing to do. The column may not be selective enough.

In any case, in the next version of SQL Server, SQL 2005 which currently
is in beta, there is no longer any sysobjects table. There is a sysobjects
view which is built on top of the new catalog views, which in their turn
are built on tables that are not exposed at all.

> I actually came across the problem when I tried to apply the
> script referenced in KB 293177 when it failed to complete.

I have no tried that script, but it may be that you should throw in an
INSENSITIVE before CURSOR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp