Re: Orphan Indexes?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 08/04/04 

Date: Wed, 4 Aug 2004 20:25:58 +0200

Ahh, perhaps you are doing the DROP inside the proc, so the parser don't know of the existence yet (as they
haven't been created at parse time... or something like that. What if you use dynamic SQL to execute the DROP
INDEX statement? 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew" <AndrewR2k1@hotmail.com> wrote in message news:uVq5dhkeEHA.2044@TK2MSFTNGP10.phx.gbl...
> Tried this already, still have the same problem.
>
>     drop index dbo.t.x
>     drop index (username).t.x
>
>     Makes no difference
>
> -- Andrew
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
> message news:%23y5RhYkeEHA.4068@TK2MSFTNGP11.phx.gbl...
> > Perhaps an owner issue for the table? You can specify the table owner in
> DROP INDEX, as below shows:
> >
> > create table t(c1 int)
> > create index x on t(c1)
> > drop index dbo.t.x
> >
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Andrew" <AndrewR2k1@hotmail.com> wrote in message
> news:%23xuiWBkeEHA.1732@TK2MSFTNGP09.phx.gbl...
> > > Hey all,
> > >
> > > I have run into a problem and I am completely lost..  I wrote a couple
> > > stored procedures to be used by a little helper program to delete and
> remake
> > > a table, upload some data to the DB from a text file the program read
> in,
> > > and finally, apply a couple indexes to the newly made table
> (TempPri2004).
> > >
> > > I ran into a snag in that when the program ran the SP's, the owner of
> the
> > > table is not the same as when I created the SP's in Query Analyzer.  So,
> I
> > > added to the program a step that would run the SP "sp_changeobjectowner"
> to
> > > change the owner so my SP's would run.  But now I have a major problem.
> > >
> > > I can no longer get rid of the indexes my SP created in the final step
> of
> > > the process.  If I run the following query, I get a record back with the
> > > Index name (it was found, in other words):
> > >
> > > "SELECT name FROM sysindexes WHERE name = 'NC_RaceLabel'"
> > > Records returned: NC_RaceLabel
> > >
> > > But if I try the following, it bombs with the error given:
> > >
> > > "DROP INDEX TempPri2004.NC_RaceLabel"
> > > Error: Cannot drop the index 'TempPri2004.NC_RaceLabel', because it does
> not
> > > exist in the system catalog.
> > >
> > > If I open SQL Server Enterprise Manager, expand the database for this
> table,
> > > right click the table, and select Manage Indexes, there is no items
> listed
> > > in the box.
> > >
> > > I am at a loss here.  The indexes are listed in the "sysindexes" table
> if I
> > > query the table, but I am unable to drop them.  Help??
> > >
> > > -- Andrew
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Orphan Indexes?
    ... You can specify the table owner in DROP INDEX, ... > I ran into a snag in that when the program ran the SP's, the owner of the> table is not the same as when I created the SP's in Query Analyzer. ... But now I have a major problem. ... > If I open SQL Server Enterprise Manager, expand the database for this table,> right click the table, and select Manage Indexes, there is no items listed> in the box. ...
    (microsoft.public.sqlserver.programming)
  • Re: No access at sql 2000 DB after restore on an other sql server
    ... i've create ^the db owner user on the new server and executed ... i start query analyzer and login as the db owner ... Apparently your default schema on the server is not the schema where the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Orphan Indexes?
    ... > Perhaps an owner issue for the table? ... You can specify the table owner in ... > Tibor Karaszi, SQL Server MVP ... >> table is not the same as when I created the SP's in Query Analyzer. ...
    (microsoft.public.sqlserver.programming)
  • Re: table/fields list
    ... Those views are stored in the master database. ... The owner is ... Need SQL Server Examples check out my website at ... It is better to query these then going directly ...
    (microsoft.public.sqlserver.programming)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)