删除SQL Azure数据库上的索引时出错:关键字"ON"附近的语法不正确(用户上下文= dbo) [英] Error dropping index on SQL Azure database: Incorrect syntax near the keyword 'ON' (user context = dbo)

问题描述

我在SQL Azure中有一个数据库，我想使用一个脚本删除所有列存储索引.

I have a database in SQL Azure and I am wanting to use a script to drop all the column store indexes.

我正在使用SQL Server的SQL管理员登录名使用SSMS进行连接.

I am connecting using SSMS using the SQL admin login of the SQL Server.

我正在使用此脚本:

declare @sql nvarchar(max);
set @sql = N'';
select @sql = @sql + N'DROP INDEX ' + OBJECT_SCHEMA_NAME(i.OBJECT_ID) + '.' + i.name + N' ON ' + OBJECT_SCHEMA_NAME(i.OBJECT_ID) + '.' + o.name + ';
'
FROM sys.indexes AS i INNER JOIN sys.tables AS o ON i.[object_id] = o.[object_id]
where i.name is not null and o.name is not null and i.type_desc like '%COLUMN%'
PRINT @sql;
EXEC sp_executesql @sql;

示例语句:

DROP INDEX [dbo].[CCI_MyTable] ON [dbo].[MyTable];  

运行时，会产生错误:

关键字"ON"附近的语法不正确.

Incorrect syntax near the keyword 'ON'.

如果我只是尝试:

 DROP INDEX [dbo].[CCI_MyTable]  

这会产生错误:

无法删除索引'dbo.CCI_MyTable'，因为它不存在或您没有权限.**

Cannot drop the index 'dbo.CCI_MyTable', because it does not exist or you do not have permission.**

在SSMS中，我可以看到[master]数据库中存在SQL SERVER admin用户，但在我正在使用的数据库中不存在.

In SSMS, I can see the SQL SERVER admin user exists in the [master] database, but does not exist in the DATABASE I am working in.

在此数据库中，我以'dbo'身份运行:

Within this DATABASE, I am running as 'dbo':

SELECT USER_NAME()      -- DBO  
SELECT CURRENT_USER;    -- DBO  

dbo是否没有删除索引的权限?

Shouldn't dbo have permissions to drop indexes?

询问:
解决这个问题的正确方法是什么?我需要将管理员用户添加到该数据库吗?如果该用户存在，并且我连接了SSMS，那么user_name()会成为该用户，而不是dbo吗?

ASK:
What is the proper way to go about this? Do I need to add the admin user to this database? If that user existed, and I connect with SSMS, would user_name() then be that user rather than dbo?

推荐答案

似乎问题出在索引名称前面加上架构(尽管我发誓我读过很多例子都可以做到这一点).

It seems the problem was preceding the index name with the schema (although, I swear many examples I've read do just that).

所以正确的脚本语法是:

So the correct script syntax is:

declare @sql nvarchar(max);
set @sql = N'';
select @sql = @sql + N'DROP INDEX ' + i.name + N' ON ' + OBJECT_SCHEMA_NAME(i.OBJECT_ID) + '.' + o.name + ';
'
FROM sys.indexes AS i INNER JOIN sys.tables AS o ON i.[object_id] = o.[object_id]
where i.name is not null and o.name is not null and i.type_desc like '%COLUMN%'
PRINT @sql;
EXEC sp_executesql @sql;

这篇关于删除SQL Azure数据库上的索引时出错:关键字"ON"附近的语法不正确(用户上下文= dbo)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！