Drop index error

Dear
I would like to using script to drop index and columns automatically. When I
run the script , it occur a error :
Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
not exist in the system catalog.
I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
Can anyone point out the problem and give me a solution. Thanks
And my script is :
Declare Live2StdCur Scroll Cursor For
select Table_Name, Column_Name from Live.Information_Schema.columns Live
where not Exists (select * from Std.Information_Schema.columns Std where
Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
Order by Table_Name
For Read Only
Open Live2StdCur
Fetch First From Live2StdCur Into @.TableName, @.ColumnName
While @.@.Fetch_Status = 0
Begin
Set @.TableId = Object_id(@.TableName)
Set @.ColumnId = (select colid from syscolumns where id =
Object_id(@.TableName) and name = @.ColumnName )
-- Drop default value
Set @.constraint_name = (select name from sysobjects where parent_obj =
@.TableId and info = @.ColumnId)
EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
-- Drop index
Declare IndexCur Scroll Cursor For
select name from sysindexes where indid in (select indid from sysindexkeys
where id = @.TableId and colid = @.ColumnId) and id = @.TableId
For Read Only
Open IndexCur
Fetch First From IndexCur Into @.index_name
While @.@.Fetch_Status = 0
Begin
EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
Fetch Next From IndexCur Into @.index_name
End
Close IndexCur
-- Drop Column
EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
-- Show information
print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
End
Close Live2StdCur
Deallocate IndexCur
Deallocate Live2StdCurTypically the indexes beginning with "_WA_Sys_" are statistics that SQL
Server generates. You ought to exclude them from your script by adding
and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
to your inner cursor where you deal with the indexes.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:

>Dear
>I would like to using script to drop index and columns automatically. When
I
>run the script , it occur a error :
>Cannot drop the index 'ADDRESS._WA_Sys_SHORTNAME_5A2A0B13', because it does
>not exist in the system catalog.
>I don't know what is the problem here and the "XXX_WA_Sys_XXXX" work for,
>Can anyone point out the problem and give me a solution. Thanks
>And my script is :
>Declare Live2StdCur Scroll Cursor For
>select Table_Name, Column_Name from Live.Information_Schema.columns Live
>where not Exists (select * from Std.Information_Schema.columns Std where
>Live.Table_Name = Std.Table_Name and Live.Column_Name = Std.Column_Name)
>Order by Table_Name
>For Read Only
>Open Live2StdCur
>Fetch First From Live2StdCur Into @.TableName, @.ColumnName
>While @.@.Fetch_Status = 0
>Begin
>Set @.TableId = Object_id(@.TableName)
>Set @.ColumnId = (select colid from syscolumns where id =
>Object_id(@.TableName) and name = @.ColumnName )
>-- Drop default value
>Set @.constraint_name = (select name from sysobjects where parent_obj =
>@.TableId and info = @.ColumnId)
>EXEC ('ALTER TABLE ' +@.TableName + ' DROP CONSTRAINT ' + @.constraint_name)
>-- Drop index
> Declare IndexCur Scroll Cursor For
> select name from sysindexes where indid in (select indid from sysindexkey
s
>where id = @.TableId and colid = @.ColumnId) and id = @.TableId
> For Read Only
> Open IndexCur
> Fetch First From IndexCur Into @.index_name
> While @.@.Fetch_Status = 0
> Begin
> EXEC ('Drop Index ' + @.TableName + '.' + @.index_name)
> Fetch Next From IndexCur Into @.index_name
> End
> Close IndexCur
>-- Drop Column
>EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
>-- Show information
>print 'Table = ' + @.TableName + ', Column = ' + @.ColumnName
>Fetch Next From Live2StdCur Into @.TableName, @.ColumnName
>End
>Close Live2StdCur
>Deallocate IndexCur
>Deallocate Live2StdCur
>
>
>|||Dear Mike
Thanks for your reply. I have solved the problem
but there is another problem here. It seem like the index type cannot drop
again
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Should I add any criteria again on the select statment
Gary
"Mike Hodgson" wrote:

> Typically the indexes beginning with "_WA_Sys_" are statistics that SQL
> Server generates. You ought to exclude them from your script by adding
> and INDEXPROPERTY([id], [name], N'IsStatistics') = 0
> to your inner cursor where you deal with the indexes.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Gary wrote:
>
>|||Sorry
The whole error message are:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'CONSTRAINT'.
Server: Msg 5074, Level 16, State 8, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
access this column.
"Gary" wrote:
> Dear Mike
> Thanks for your reply. I have solved the problem
> but there is another problem here. It seem like the index type cannot drop
> again
> The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
> Should I add any criteria again on the select statment
> Gary
> "Mike Hodgson" wrote:
>|||Gary,
Cursor loops (especially nested loops) can be difficult to debug. What
I often do, which I find quite helpful especially when building up
complex dynamic strings to EXEC at runtime, is change the "EXEC (...)"
statements to "PRINT (...)" so I can see exactly what T-SQL commands the
SQL server is trying to execute. Then you can take the results of that
(with all the print statements) and, as that should be a valid T-SQL
script, execute the statements either one at a time (in order) or in
small batches to see where it's going wrong. It often becomes blatantly
obvious where the mistake is when you do this.
One thing I notice is you are declaring the inner cursor (IndexCur)
inside the outer WHILE loop but you're only deallocating it outside the
outer loop. The OPEN & CLOSE are fine and open & close the resultset
appropriately, but how you've got the DECLARE & DEALLOCATE I would think
would result in the same cursor being declared multiple times but the
resources used by the cursor would not get released each time. SQL
Server may be able to handle this odd case (not sure), but in most
programming languages that would result in a memory/resource leak.
I think the problem is in the fact that you're using variables in the
declaration of your inner cursor (IndexCur) but since you're not
deallocating the cursor before you declare it again (i.e. at the end of
the inner loop), the different variable values each time through the
outer loop will not be taken into account when the cursor is declared
again. This would mean that for each iteration of the outer loop, the
inner cursor would have the same declaration and so you'd be working on
the same resultset for IndexCur each time...I think. BOL describes this
on its "DECLARE CURSOR" page:
Variables may be used as part of the /select_statement/ that
declares a cursor. Cursor variable values do not change after a
cursor is declared. In SQL Server version 6.5 and earlier, variable
values are refreshed every time a cursor is reopened.
I may be off-base with this thought but you should be able to tell if
this is the case or not pretty quickly by changing your "EXEC (...)"
statements to "PRINT (...)" and looking at the resultant T-SQL statements.
It's always a good idea to have your DECLARE/DEALLOCATE and OPEN/CLOSE
statements at the same scope as each other so they're always a matching
pair in terms of scope.
Also, there's not much point in making the cursors SCROLL cursors since
the only operation you're doing on them is FETCH NEXT (the FETCH FIRST
statements in this context do the same as a FETCH NEXT).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gary wrote:
>Sorry
>The whole error message are:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'CONSTRAINT'.
>Server: Msg 5074, Level 16, State 8, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698DIMIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 5074, Level 16, State 1, Line 1
>The index 'I_698PROJIDIDX' is dependent on column 'INVENTPROJID'.
>Server: Msg 4922, Level 16, State 1, Line 1
>ALTER TABLE DROP COLUMN INVENTPROJID failed because one or more objects
>access this column.
>
>"Gary" wrote:
>
>