Wie führt man eine Mehrfachabfrage unter Verwendung von sp_MSforeachtable aus?

Question

Wie füge ich jedem Tisch mit sp_MSforeachtable einen Index hinzu? Es gibt immer Fehlermeldungen.

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]
'

Ein solcher Fehler:

Msg 137, Level 15, State 1, Zeile 2
Muss die skalare Variable "@idx" deklarieren.

Ich versuche das declare innerhalb des Befehls zu platzieren, aber dann erhalte ich diesen Fehler:

Msg 4104, Level 16, State 1, Zeile 3
Der mehrteilige Bezeichner "dbo.DIAG_contractAuditHistory" konnte nicht gebunden werden.

Hier ist ein aktualisierter Versuch, Syntaxfehler zu beheben, mit Test-SQL, aber es gab immer noch seltsame Fehler. Letztendlich löste ich das Problem mit einem gespeicherten Verfahren und rufte das gespeicherte Verfahren für jeden Tisch auf.

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'

Dies würde für etwa zwei Dutzend Tabellen funktionieren, dann gibt es seltsame Fehler wie

Msg 1934, Level 16, State 1, Zeile 9 CREATE INDEX fehlgeschlagen, da die folgenden SET-Optionen falsch eingestellt sind: 'QUOTED_IDENTIFIER'. Stellen Sie sicher, dass SET-Optionen korrekt sind für die Verwendung mit indizierten Ansichten und/oder Indizes auf berechneten Spalten und/oder gefilterten Indizes und/oder Abfragebenachrichtigungen und/oder XML-Datentypmethoden und/oder Operationen mit räumlichen Indizes.

Also habe ich eine andere Strategie verwendet und ein gespeichertes Verfahren erstellt, um die Indizes zu erstellen: Wie erstelle ich einen Index innerhalb eines gespeicherten Verfahrens?

Answer 1

Ich habe noch nie versucht, @precommand zu verwenden, aber wenn du diesen Teil zu @command1 hinzufügst, sollte es funktionieren.

Das zweite Problem hat damit zu tun, wie du ? verwendest, es enthält bereits das dbo-Schema. Ersetze den Teil OBJECT_ID(N''[dbo].[?]'') durch OBJECT_ID("?") und dasselbe gilt für die CREATE und DROP INDEX Anweisungen.

EDIT:

Dieser Code sollte funktionieren.

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]
    '

Answer 2

Sie können idx im Befehl deklarieren. Sie haben auch andere Syntaxfehler. Dein Fehler hat nichts mit der Deklarationsanweisung zu tun. Es liegt daran, dass du ? falsch verwendest. sp_msforeachtable führt einen einfachen Ersatz durch. Also für deine Verwendung musst du es in Anführungszeichen setzen (oder besser gesagt, die äußeren Zeichen nicht entfernen).

Sehen Sie meine Antwort auf deine andere Frage, warum das immer noch nicht funktionieren wird.

Hier ist eine ausgebesserte Version von Hand:

EXEC sp_MSforeachtable
@command1 = '
declare @idx nvarchar(1000);
declare @tbl nvarchar(1000);
set @tbl = replace(''?'', ''[dbo].'', '''');
set @tbl = left(@tbl, len(@tbl) - 1);
set @idx = ''idx_'' + @tbl + ''_modified_on'';
print @idx;
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''?'') AND name = N''@idx'')
    DROP INDEX [@idx] ON ?;
declare @sql nvarchar(1000);
set @sql = ''
CREATE NONCLUSTERED INDEX ['' + @idx + ''] ON ? 
(
    [modified_on] ASC
) ON [PRIMARY]'';
exec sp_executesql @sql;
';

Ich sollte mich fragen, warum du das machst. Nur dieses Feld zu indizieren ist nicht so gut, wie du vielleicht denkst. Informieren Sie sich über Covering-Indizes.