Dropping An Indexed Column

I have inherited a table with dozens of columns that I no longer want. I want to drop these columns.

So I tried
"ALTER TABLE mydata DROP BLOCK_ID"

and it get an error of: cannot delete a field that is part of an index. How do I get around this?

(BLOCK_ID is the field name of my indexed column)
(The non-indexed ones drop fine.)First remove the column you want to drop from all the indexes that refer it. If there are indexes that refer only that column just drop those. Then you can drop the column.

Cheers,
Suren.|||Yes, I get that I have to drop the index(es) -- but how do I find out which indexes this column is in?

I'm building up to write some scripts to automatically drop a long-list of unwanted columns - how does one go about figuring out what index a field is in? And/or is there a sql way of saying "drop this column and it's indexes" ?|||Well to do that the mist easiest way is to use a graphical tool that organise indexes unser each table and to go through the index and remove the coloms.

If you are thinking of writing scripts then you should select from catalog tables such as user_indexes and user_inx_cols. I think I got the names correct.

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!
You should have made a transaction log backup on the 25:th instead of a database backup.
In SQL Server, you can restore a database backup and then a number of transaction log backups. You
cannot skip over a log backup, as all log records need to be applied one after the other. This seems
to be your situation, that you are missing a log backup. You can look in the backup history tables
in msdb to see when the log backups were performed and to where. These tables has a rather complex
structure, though. Also, there are some operations that just cut the log without doing a backup.
This will also infer with a log backup restore sequence. I suggest you open a case with MS PSS so
they can help you through this situation as this is very difficult to do over a newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!
sql

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!You should have made a transaction log backup on the 25:th instead of a database backup.
In SQL Server, you can restore a database backup and then a number of transaction log backups. You
cannot skip over a log backup, as all log records need to be applied one after the other. This seems
to be your situation, that you are missing a log backup. You can look in the backup history tables
in msdb to see when the log backups were performed and to where. These tables has a rather complex
structure, though. Also, there are some operations that just cut the log without doing a backup.
This will also infer with a log backup restore sequence. I suggest you open a case with MS PSS so
they can help you through this situation as this is very difficult to do over a newsgroup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!You should have made a transaction log backup on the 25:th instead of a data
base backup.
In SQL Server, you can restore a database backup and then a number of transa
ction log backups. You
cannot skip over a log backup, as all log records need to be applied one aft
er the other. This seems
to be your situation, that you are missing a log backup. You can look in the
backup history tables
in msdb to see when the log backups were performed and to where. These table
s has a rather complex
structure, though. Also, there are some operations that just cut the log wit
hout doing a backup.
This will also infer with a log backup restore sequence. I suggest you open
a case with MS PSS so
they can help you through this situation as this is very difficult to do ove
r a newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx
.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!