January 4, 2010 at 7:55 am
i have a database with archived data and want to get rid of most indexes there to free up storage. wrote a cursor and it runs but never ends and when i stop it i get the following error
Could not find stored procedure 'drop index idx_bank on profilesATT063009 over and over. it's one of the indexes.
declare @index_name varchar(255)
declare @table_name varchar(255)
declare @sql varchar(255)
declare index_drop cursor for
select top 1 a.name, b.name from sys.indexes a
inner join sys.objects b on a.object_id = b.object_id
where a.type_desc = 'NONCLUSTERED'
and b.type_desc != 'SYSTEM_TABLE'
and a.name not like 'PK%'
OPEN index_drop
FETCH NEXT FROM index_drop
INTO @index_name, @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'drop index ' + @index_name + ' on ' + @table_name + ';'
exec @sql
END
CLOSE index_drop
DEALLOCATE index_drop
January 4, 2010 at 8:29 am
SQL Noob, your cursor declaration has a top clause so your resultset only has one record. You are also not fetching the next record in the cursor so SQL Server is attempting to drop the same index over and over again.
Try this:
declare @index_name varchar(255)
declare @table_name varchar(255)
declare @sql varchar(255)
declare index_drop cursor for
select a.name, b.name from sys.indexes a
inner join sys.objects b on a.object_id = b.object_id
where a.type_desc = 'NONCLUSTERED'
and b.type_desc != 'SYSTEM_TABLE'
and a.name not like 'PK%'
OPEN index_drop
FETCH NEXT FROM index_drop
INTO @index_name, @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'drop index ' + @index_name + ' on ' + @table_name + ';'
exec @sql
FETCH NEXT FROM index_drop
INTO @index_name, @table_name
END
CLOSE index_drop
DEALLOCATE index_drop
January 4, 2010 at 8:38 am
You need brackets around the parameter to the exec.
EXEC @sql -- SQL assumes that @sql contains he name of a stored procedure
EXEC (@sql) -- SQL will execute the contents of the string.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2010 at 9:12 am
nice, thank you
ran this and it dropped over 1000 indexes in a few seconds. we have a process to copy some tables to an archive db every month and i want the PK's copied but not the other indexes. for some reason my package won't copy the PK's if i have it specified not to copy indexes so i need a script to drop indexes that i don't want.
declare @index_name varchar(255)
declare @table_name varchar(255)
declare @sql varchar(255)
declare index_drop cursor for
select a.name, b.name from sys.indexes a
inner join sys.objects b on a.object_id = b.object_id
where a.type_desc = 'NONCLUSTERED'
and b.type_desc != 'SYSTEM_TABLE'
and a.name not like 'PK%'
--and b.name = 'profilesATT063009'
OPEN index_drop
FETCH NEXT FROM index_drop
INTO @index_name, @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'drop index ' + @index_name + ' on ' + @table_name
exec (@sql)
FETCH NEXT FROM index_drop
INTO @index_name, @table_name
END
CLOSE index_drop
DEALLOCATE index_drop
January 4, 2010 at 9:19 am
What's about unique indexes? Do you want to drop those as well?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2010 at 9:37 am
the data isn't used very much after the archive so it doesn't have to be exact. just worried about storage.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply