Dropping Multiple Indexes with Subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dropping Multiple Indexes with Subquery

Author

Topic

thunt
Starting Member

7 Posts

Posted - 2010-11-30 : 13:48:36

Hello,

I am new to the forum and new to T-SQL. I need to drop multiple indexes with one drop index or alter table command and need to have the list of indexes included in the drop be constrained by a subquery to select indexes that start with a specific set of characters. Here is what I have tried to use (unsuccessfully, but you get the idea):

GO
DECLARE @nsiGetIndexId varchar(1000);
SET @nsiGetIndexId =
(SELECT
name + ','
FROM
sys.indexes
where
OBJECT_NAME(object_id) like 'nsi%'
AND
is_primary_key = 0
AND
is_unique_constraint = 0)

SELECT @nsiGetIndexId

DROP INDEX (select @nsiGetIndexId)
GO

Thank you for any insight you can provide.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-30 : 14:06:23

You'll need dynamic SQL for this, and the drop can only be for a single index at a time.

--
Gail Shaw
SQL Server MVP

thunt
Starting Member

7 Posts

Posted - 2010-11-30 : 14:09:02

quote:
Originally posted by GilaMonster

You'll need dynamic SQL for this.

--
Gail Shaw
SQL Server MVP

Thank you Gail. I am trying to use a cursor. Might that work?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-30 : 15:34:09

Yes.

--
Gail Shaw
SQL Server MVP

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-30 : 15:57:51

Here's a query to do the trick, copy and paste the results into a new window to run:

SELECT 'DROP INDEX ' + quotename(name) + ' ON ' + quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id))
FROM sys.indexes 
WHERE is_primary_key = 0 AND is_unique_constraint = 0
AND OBJECT_NAME(object_id) like 'nsi%'

thunt
Starting Member

7 Posts

Posted - 2010-12-01 : 07:46:58

Thank you, robvolk. Much appreciated.

thunt

thunt
Starting Member

7 Posts

Posted - 2010-12-02 : 14:02:30

Hi robvolk,

Below is what I ended up with based on your decision. Am I missing the concept because when I run this and there is more than one index returned via the dynamic query, it errors out. Please and thanks for your (or anyone's) insight.

GO
use nsi;
BEGIN TRANSACTION;
DECLARE @nsiDropIndexes nvarchar(1000);
SET @nsiDropIndexes =
(
SELECT
'DROP INDEX ' + quotename(name) + ' ON ' + quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id))
FROM
sys.indexes
WHERE
is_primary_key = 0
AND
is_unique_constraint = 0
AND
OBJECT_NAME(object_id) like 'nsi%'
)
EXEC sp_executesql @nsiDropIndexes
COMMIT TRANSACTION;
GO

thunt

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-02 : 15:00:50

Try this:

use nsi;
BEGIN TRANSACTION;
DECLARE @nsiDropIndexes nvarchar(1000);
SELECT @nsiDropIndexes = IsNull(@nsiDropIndexes, '') +
';DROP INDEX ' + quotename(name) + ' ON ' + quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id))
FROM 
sys.indexes 
WHERE 
is_primary_key = 0 
AND 
is_unique_constraint = 0
AND 
OBJECT_NAME(object_id) like 'nsi%'
EXEC sp_executesql @nsiDropIndexes
COMMIT TRANSACTION;
GO

thunt
Starting Member

7 Posts

Posted - 2010-12-02 : 15:20:36

Thank you, robvolk. I ended up using a cursor. It seems to work ok. Here is my script. Please advise, do you envision any issues? Thank you.

use nsi; -- use the nsi database
GO
DECLARE @v_nsi_drop_indexes nvarchar(4000)

DECLARE c_nsi_indexes CURSOR FOR
SELECT
'DROP INDEX ' + name + ' ON ' + object_schema_name(object_id) + '.' + object_name(object_id)
FROM
sys.indexes
WHERE
is_primary_key = 0
AND
is_unique_constraint = 0
AND
OBJECT_NAME(object_id) like 'nsi%'

OPEN c_nsi_indexes

FETCH NEXT FROM c_nsi_indexes
INTO @v_nsi_drop_indexes

WHILE @@FETCH_STATUS = 0 -- while fetch statement is successful
BEGIN
EXEC sp_executesql @v_nsi_drop_indexes
FETCH NEXT FROM c_nsi_indexes
INTO @v_nsi_drop_indexes
END
CLOSE c_nsi_indexes
DEALLOCATE c_nsi_indexes;
GO

thunt

thunt
Starting Member

7 Posts

Posted - 2010-12-02 : 15:31:22

Hi robvolk,

If I may also ask, with regard to your use of "quotename", I referenced the following:

"Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier"

May I ask, under what circumstances might my elimination of the delimiters result in a problem for me? Thank you.

thunt

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-02 : 16:17:26

If the object name contains spaces, punctuation characters, or begins with a non-alpha character, you'll have to quote the name or else you'll get a syntax error. It's a good practice to always quote object names, and QUOTENAME() does exactly that for strings.

Nothing wrong with using the cursor for this operation. Did the code I sent earlier not work?

thunt
Starting Member

7 Posts

Posted - 2010-12-03 : 09:03:44

Hi robvolk,

Thank you for your response to my question with regard to QUOTENAME. I appreciate your insight and will update my script to include that. Thank you, as well, for confirming with regard to the cursor. Yes, your code worked fine. It is just that I had already completed the cursor approach by the time I read your message from 15:00 yesterday. Learning about the use of cursors was a good thing and your insight has been really helpful. Thank you, again.

thunt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources