How to drop a table name like "New Text Document" in sybase iq 16sp08 ?

[Former Member]

In our organization we have to drop some schemas for that we have to drop all the tables and sp's  of those schemas, we have a problem here one table

like "New Text Document" exist in one schema we are unable to  drop this table, when we try to drop it says table does not exist. But it exits it systab output. So we are unable to drop a schema now.

I tried this statements

drop table "New Text Document";

drop table "krish.New Text Document"

drop table 'krish.New Text Document'

drop table 'New Text Document';

we are unable select this table also.

thanks in advance

Regards

Krishnam Raju.P

[tayeb_hadjou · Accepted solution]

Hi,

Make sure option Quoted_Identfier is enabled (ON : by default  in dbisql and odbc connections).

sp_iqcheckoptions;

If off, enable it :

Set temporary option QUOTED_IDENTIFIER='ON' ;

drop "krish"."New Text Document" ;

If still fais then display table information using query:

sp_iqhelp "New Text Document";

or

select * from SYSTAB where lower(table_name) like '%new text document%'

order by table_name ;

[Former Member · Accepted solution]

Hi Tayeb,

Thanks for reply, I don't find the QUOTED_IDENTIFIER option name in sp_iqcheckoptions; output.

I tried below but no use.

Set temporary option QUOTED_IDENTIFIER='ON' ;

drop "krish"."New Text Document" ;

error popup  like syntax error near 'krish'

we get output for below two queries

select * from SYSTAB where lower(table_name) like '%new text document%'

order by table_name ;

sp_iqhelp "New Text Document";

[tayeb_hadjou · Accepted solution]

This means it is set to default (=ON).

System procedure sp_iqcheckoptions displays only NON-default setting.

[tayeb_hadjou · Accepted solution]

Sorry for the mistyping. Add keyword table :

drop table "krish"."New Text Document" ;

I tested  commands below with success:

create table "new testtable demo" (col1 int, col2 varchar(20)) ;

sp_iqtable "new testtable demo" ;

Result:

Table_name,Table_type,Table_owner,Server_type,location,dbspace_id,isPartitioned,Remarks,table_constraints,PartitionType,isRlv 'new testtable demo','BASE','DBA','IQ','Main',16384,'N',,,'None','F'

drop table "DBA"."new testtable demo"

[Former Member · Accepted solution]

yes now table was dropped .Thank for your help Tayeb. I have another small question is there any way to check who has the execution or access permission of stored procedures like systableperm for tables.

[tayeb_hadjou · Accepted solution]

System views: SYSPROCAUTH, SYSPROCPERM.

Each row in the SYSPROCAUTH view describes a set of privileges granted on a procedure. As an alternative, you can also use the SYSPROCPERM system view.

[Former Member · Accepted solution]

Thanks Tayeb.

[markmumy · Accepted solution]

Don't forget, too, that you don't need to use quotes all the time.  IQ supports brackets around the names.  It has the added benefit of not needing to set options.

create table [new testtable demo] (col1 int, col2 varchar(20)) ;

sp_iqtable [new testtable demo] ;

drop table [DBA].[new testtable demo];

Mark

[Former Member · Accepted solution]

Thank for reply Mark.