CODEWITHSUNDEEP
sqlt-sqlsybasesap-ase
insidesin
insidesin

Reputation: 753

Drop columns in Sybase ASE table

How can I drop a column in a Sybase ASE 15 table setup.

I have tried a few, to no avail:

alter table my_table
drop column1, column2

As well as looking at the Sybase documentation which provides no solution to my issues.

The 'select into' database option is not enabled for database 'my_db'. ALTER TABLE with data copy cannot be done. Set the 'select into' database option and re-run.

Upvotes: 2

Views: 19179

Answers (5)

Kudlaty U
Kudlaty U

Reputation: 31

Just want to share with you I've just had similar issue, and in my case I had to add "drop" before each column name. The code looks like this:

alter table dba.ABC
drop column1,
drop column2,
drop column3

commit;

I work on some old version of sybase that is not that flexible. Cheers

Upvotes: 3

Ben Slade
Ben Slade

Reputation: 508

Interesting, the fact that "select into" has to be turned on means that the Sybase server is internally copying the data from the old table to a new copy of the table, without using the transaction logs (or using transaction logs only to log the allocation of pages, not the data change within the page). Ie. sort of like bulkcopy. I wonder if this causes any problems for replication?

Also, just for completeness, the "no datacopy" feature is new, as of version 15.7.1.

Upvotes: 1

insidesin
insidesin

Reputation: 753

I found the solution. My new test database had the 'select into' option turned off, meaning the removal/alterations of tables were being rejected.

i.e.

sp_dboption my_db, "select into", true

It all looks so obvious now that I know what the options are.

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

As your select into option on database is off, there are two option either you ON the select into option using 

sp_dboption my_db, "select into", true

OR

The no datacopy parameter to the alter table drop column allows you to drop columns from a table without performing a data copy, and reduces the amount of time required for alter table drop column to run.

SYBASE Documentation - Dropping Columns from a Table Without Performing a Data Copy

ALTER TABLE my_table
DROP column1, column2
WITH no datacopy

Upvotes: 9

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

You forgot to use the keyword 'COLUMN' in your query. Use following syntax:-

ALTER TABLE my_table
DROP COLUMN column1, column2

Upvotes: 1

Related Questions