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.