sql - 如何使用 sp_MSforeachtable 执行多行查询

Question

如何使用 sp_MSforeachtable 向每个表添加索引？它不断给出错误。

EXEC sp_MSforeachtable @precommand = 'declare @idx as char;',
@command1 = '
set @idx = ''idx_'' + ? + ''_modified_on'';
print @idx;
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[?]'') AND name = N''@idx'')
    DROP INDEX [@idx] ON [dbo].[?]

CREATE NONCLUSTERED INDEX [@idx] ON [dbo].[?] 
(
    [modified_on] ASC
) ON [PRIMARY]
'

一个这样的错误:

Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@idx".

我尝试将声明放在命令中，但随后出现此错误:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dbo.DIAG_contractAuditHistory" could not be bound.

这是修复语法错误的更新尝试，还有测试 SQL，但它仍然给出了奇怪的错误。我最终用一个存储过程解决了这个问题，并为每个表调用了这个存储过程。

EXEC sp_MSforeachtable @command1 = '
declare @idx as varchar(256);
set @idx = ''idx_'' + SUBSTRING(''?'', 8, len(''?'')-8) + ''_modified_on'';
print @idx;
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''?'') AND name = @idx   )
    DROP INDEX [@idx] ON ?

IF EXISTS (select * from sys.columns where object_id = OBJECT_ID(N''?'') and name = ''modified_on'')
    CREATE NONCLUSTERED INDEX [@idx] ON ?
    (
        [modified_on] ASC
    ) ON [PRIMARY]
'

declare @cat as char;
set @cat='dog';
print @cat;

EXEC sp_MSforeachtable 'print ''idx_'' + SUBSTRING(''?'', 8, len(''?'')-8) + ''_modified_on'';'
EXEC sp_MSforeachtable 'print ''?'''
print substring('[dbo].[merchantNotes]', 8, (len('[dbo].[merchantNotes]')-9))

select * from sys.columns where object_id = OBJECT_ID(N'[dbo].[banks]') and name = 'modified_on'
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'contractPaymentHistory') AND name = 'idx_contractPaymentHistory_modified_on'

这适用于大约两打表，然后它会给出奇怪的错误，例如

Msg 1934, Level 16, State 1, Line 9 CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

所以我最终使用了不同的策略并创建了一个存储过程来创建索引: How do I create an index inside a stored procedure?

Answer 1

我从未尝试过使用 @precommand之前，但如果您将该部分添加到 @command1它应该工作。

第二个问题与您如何使用 ? 有关。 ，它实际上已经包含 dbo 架构。更换零件 OBJECT_ID(N''[dbo].[?]'')来自 OBJECT_ID("?") CREATE 也是如此和 DROP INDEX声明。

编辑:

这段代码应该可以工作。

EXEC sp_MSforeachtable
    '
        declare @idx as char;
        set @idx = ''idx_'' + "?" + ''_modified_on'';
        IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID("?") AND name = N''@idx'')
            DROP INDEX [@idx] ON ?

        CREATE NONCLUSTERED INDEX [@idx] ON ?
        (
            [modified_on] ASC
        ) ON [PRIMARY]
    '