4

I had a problem with an index. The index name is "dbo.indexname" (including '"') and I cannot delete it. Someone knows how to drop an index with that name?

sp_help table output:

index_name, index_keys, index_description,  index_max_rows_per_page,       index_fillfactor,    index_reservepagegap,   index_created,  index_local

"dbo.index_name",    id, nonclustered, 0,           0,       0, Nov 27 2015  6:41PM,    Global Index

Output for: select * from sysindexes where name like '%dbo.index_name%'

name    id  indid   doampg  ioampg  oampgtrips  status3 status2 ipgtrips    first   root    distribution    usagecnt    segment status  maxrowsperpage  minlen  maxlen  maxirow keycnt  keys1
"dbo.index_name"    1259148500  3   0   0   0   0   0   0   0   0   0   0   1   0   0   15  15  15  2   81  00  06  00  01  00  00  bf  08  00  00  00  01  00  00  00  81  00  00  00  00  00  00  2d  06  00  00  00  09  00  00  00
Gardella Juan
  • 382
  • 5
  • 12
  • `drop index ["dbo.indexname"]`? – Marc B Dec 01 '15 at 19:41
  • DROP INDEX TABLE."dbo.indexname" / or ["dbo.indexname"] or \"dbo.indexname\" The error is: DROP INDEX does not allow specifying the database name as a prefix to the object name ErrorCode: 166 – Gardella Juan Dec 01 '15 at 19:42
  • 1
    Is this ASE? this is how you drop in ASE: `drop index <>.["indexname"]`
     – Meet Dec 02 '15 at 05:19
  • Meet is right, but just to add - make sure you are running the command as user 'dbo', since the drop index command does not allow you to specify user name, and you may have same table name + index name for multiple users (schemas). – Alex Dec 02 '15 at 14:33
  • it didn't work. Same error. I tried: drop index table.["dbo.indexname"] and drop index table.[""dbo.indexname""] with the same result. – Gardella Juan Dec 02 '15 at 17:18
  • Is "dbo" the database name? Seems the parser is splitting on the "." and complains because the index belongs to a table, not the DB itself. I'd try drop index '"dbo.indexname"' (single-quoting the quoted index name) because in another page I saw that it's possible to use either single or double quotes. – Juan Lanus Dec 02 '15 at 19:55
  • http://stackoverflow.com/questions/16019007/escape-double-quote-in-sybase – Mike Gardner Dec 02 '15 at 20:12
  • Thanks, but it didn't work. "dbo" is a DB user, no DB name. It didn't work with: drop index tablename.["'dbo.indexname'"]. Same error. – Gardella Juan Dec 02 '15 at 20:57
  • what is the name of your table and your index? (you should not specify 'dbo' anywhere in command, I realize it's counter intuitive) – Alex Dec 02 '15 at 21:38
  • TABLE: table_name INDEX: "dbo.table_name_index" USER: dbo DB: DBName – Gardella Juan Dec 02 '15 at 21:38
  • can you run this command and add the output to your post (not in comment here, but in original post): sp_help 'dbo.table_name' – Alex Dec 02 '15 at 21:53
  • Done. I put sp_help output – Gardella Juan Dec 02 '15 at 22:10
  • Wow, please post answer if you figure it out. – Alex Dec 02 '15 at 22:36
  • a bug in sybase? You can create it but it's not possible to delete it. – Gardella Juan Dec 02 '15 at 23:32
  • would you please remove `dbo.`!! As already mentioned, you can not have db user preceding index name – Meet Dec 03 '15 at 04:03
  • Ignore previous comment (didn't realize dbo. was actually part of the index name). Other workaround may include creating a copy of entire table, truncating and dropping old table, renaming the new table and finally creating a fresh index without `dbo.` – Meet Dec 03 '15 at 04:31
  • Can you rename the index first to something else, then drop it? `sp_rename objname, newname [,“index” | “column”]` http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs185.htm – Mike Gardner Dec 03 '15 at 13:16
  • if possible; stop all operations, copy data to another table. drop your table, create as you desired. Finally move data to final table. It will rescue you forever. And please give very specific names to your indexex. Plus, you can try dbcc check commands. – Burcin Dec 04 '15 at 09:56

1 Answers1

0

What is the name of the table? Let's assume 'mytab'. Now do this:

set quoted_identifier on go drop index mytab."dbo.indexname" go

For some background on why this is puzzling, see http://www.sypron.nl/quiz2002a.html#dec02 .

RobV
  • 2,263
  • 1
  • 11
  • 7
  • You need to post your command and the error otherwise this cannot be resolved. – RobV Dec 04 '15 at 20:16
  • Also run this and post the output: SELECT object_name(id), name from sysindexes where name like '%indexname%' – RobV Dec 04 '15 at 20:48
  • You did not include the first column, namely 'object_name(id). "select *' does not show the table name. – RobV Dec 06 '15 at 09:44