Drop and add index usig a variable

Hello,

Trying to drop index on a table. But doesn't seem to work

DECLARE @indexName VARCHAR(50);
SELECT @indexName =  i.name
FROM sysobjects o, sysindexes i
WHERE  (o.id = i.id and o.name = 'MyTable') AND i.name like ('%NonClustered%')

DROP INDEX [@indexName] ON [dbo].[MyTable]

ALTER TABLE [dbo].[Link]
ALTER COLUMN [myColumn] [nvarchar](120)

CREATE INDEX [@indexName] ON [dbo].[MyTable] (myColumn)

"Cannot drop the index 'dbo.MyTable.@indexName', because it does not exist or you do not have permission."

I am able to get the correct index name but unable to execute drop index command with a variable.
LVL 6
sansofturaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ste5anConnect With a Mentor Senior DeveloperCommented:
You cannot use a variable in the DROP statement directly. Use dynamic SQL instead. E.g.

DECLARE @indexName VARCHAR(50);
SELECT @indexName =  i.name
FROM sysobjects o, sysindexes i
WHERE  (o.id = i.id and o.name = 'MyTable') AND i.name like ('%NonClustered%');

DECLARE @Sql NVARCHAR(MAX) = N'DROP INDEX ' + QUOTENAME(@indexName) +' ON [dbo].[MyTable]';
EXECUTE (@Sql);

ALTER TABLE [dbo].[Link]
ALTER COLUMN [myColumn] [nvarchar](120);

SET @Sql = N'CREATE INDEX ' + QUOTENAME(@indexName) +'  ON [dbo].[MyTable] (myColumn);';
EXECUTE (@Sql);

Open in new window

0
 
sansofturaAuthor Commented:
Perfect!
0
All Courses

From novice to tech pro — start learning today.