views:

350

answers:

1

So, how can I drop all indexes in a SQL database with one command? I have this command that will get me all the 20 or so drop statements, but how can I run all of those drop statements from this "result set"?

select * from vw_drop_idnex;

Another variation that gives me the same list is:

SELECT  'DROP INDEX ' + ix.Name + ' ON ' + OBJECT_NAME(ID)  AS QUERYLIST
FROM  sysindexes ix
WHERE   ix.Name IS NOT null and ix.Name like '%pre_%'

I tried to do "exec(select cmd from vw_drop_idnex)" and it didn't work. I am looking for something that works like a for loop and runs the queries one by one.

-----------------------

With Rob Farleys help, final draft of the script is:

declare @ltr nvarchar(1024);
SELECT @ltr = ( select 'alter table '+o.name+' drop constraint '+i.name+';'
  from sys.indexes i join sys.objects o on  i.object_id=o.object_id
  where o.type<>'S' and is_primary_key=1
  FOR xml path('') );
exec sp_executesql @ltr;

declare @qry nvarchar(1024);
select @qry = (select 'drop index '+o.name+'.'+i.name+';'
  from sys.indexes i join sys.objects o on  i.object_id=o.object_id
  where o.type<>'S' and is_primary_key<>1 and index_id>0
for xml path(''));
exec sp_executesql @qry
+3  A: 

You're very close.

declare @qry nvarchar(max);
select @qry = 
(SELECT  'DROP INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ID) + '; '
FROM  sysindexes ix
WHERE   ix.Name IS NOT null and ix.Name like '%prefix_%'
for xml path(''));
exec sp_executesql @qry
Rob Farley
thanks a lot! i knew about that method but i was hoping for something that didnt require declaring a variable. i can make it work though.
djangofan
Also worth mentioning... you should probably consider the quotename function around your names, in case they have spaces in them.
Rob Farley
wow, that XML thing is cool. ;-)
djangofan
its not quite working. i get a message that says: Msg 102, Level 15, State 1, Line 1Incorrect syntax near '<'.
djangofan
Sounds to me like you're trying to name that column still. Make sure you don't have "AS QUERYLIST" or anything in there. While debugging, replace the line "exec sp_executesql @qry" with "select @qry" to see what you're about to run. It should become more obvious then.
Rob Farley
your last comment helped a LOT! the problem was that I needed a semi-colon at the end of each sql statement in the result set. also, this method doesn't get me the corresponding "alter table... drop contraint..." query that I need in order to drop indexes. the vw_drop_idnex stored procedure that I mentioned at the top of my question was giving me that. any idea?
djangofan
If I manually remove the offending constraint the solution works. Now I just need to figure out how to get the drop constraint statement that will allow me to drop the indexes.
djangofan