|
|
You're in the
sql server programming
group:Drop index error
sql server programming:
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 Live2StdCur
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* http://www.mallesons.com [quoted text, click to view] 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 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 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 [quoted text, click to view] "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* 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 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 Live2StdCur > > > > > > > > > >
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. [quoted text, click to view] "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: > > > 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* 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 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 Live2StdCur > > > > > > > > > > > > > > >
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 [quoted text, click to view] 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: >> >> >> >>>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* 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 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 Live2StdCur >>>> >>>> >>>> >>>> >>>> >>>>
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |