September 22, 2010 at 5:20 am
Hi there, I have a lot of unused indexes on my database which I would like to drop. Now I know how to drop an index but I would like to drop indexes with a variable passed as a parameter. Here is a normal drop syntax:
drop index MyIndex ON MyTable with (online = off)
but what I want to do is this:
drop index @index ON @table with (online = off)
I get a syntax error when I just do this so I tried this:
declare @sqlstring varchar(max)
set @sqlstring = 'drop index '+@index+' ON '+@table+' with (online = off)'
execute @sqlstring
The @table and @index variables is stored proc parameters which are both varchar(max) but this clearly does not work. I am just doing something wrong but don't know what. Please can someone help me?
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
September 22, 2010 at 5:30 am
When you execute a dynamic SQL, you need to use parentheses around the variable that stores the dynamic SQL. Also I don’t think that you need to specify that you do it online. Below is a small demo that uses your code and shows you that it works:
use tempdb
go
create table Demo (i int)
go
create index ix_demo_i on Demo (i)
go
--See that the index exists
exec sp_helpindex Demo
go
declare @index varchar(20)
declare @table varchar(20)
declare @sqlstring varchar(max)
select @index = 'ix_demo_i', @table = 'Demo'
set @sqlstring = 'drop index '+@index+' ON '+@table --+' with (online = off)'
execute (@sqlstring)
--See that now the table has no index
exec sp_helpindex Demo
go
drop table Demo
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply