How to execute multiline query using sp_MSforeachtable

Question

How do I add an index to every table using sp_MSforeachtable? It keeps giving errors.

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

One such error:

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

I try to place the declare inside the command, but then get this error:

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

---

Here is an updated attempt to fix syntax errors, with test SQL as well, but it still gave strange errors. I ended up solving the problem with a stored procedure and calling the stored procedure for each table.

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'

This would work for about two dozen tables, then it gives strange errors like

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.

So I ended up using a different strategy and created a stored procedure to create the indices: How do I create an index inside a stored procedure?

Answer 1

I have never tried using @precommand before, but if you add that part into @command1 it should work.

Second problem has to do with how you use ?, it actually already contains the dbo schema. Replace the part OBJECT_ID(N''[dbo].[?]'') by OBJECT_ID("?") and the same goes for the CREATE and DROP INDEX statements.

EDIT:

This code should work.

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

You can declare idx in the command. You have other syntax errors too. voting. Your error is nothing to do with the declare statement. It is because you are using ? wrong. sp_msforeachtable will do a simple replace. So for your usage, you need to quote it (or rather, don't unquote the outer strings.

See my answer to your other question as to why this still won't work.

Here's a freehand corrected-ish version:

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

I should question why you're doing this. Indexing that field alone is not as good as you may thing. Read up on covering indexes.