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?

@precommand isn't in the same batch, it's executed in a calling scope/batch. It's really intended to setup things that have either global or hierarchical scope (like #temp tables), DECLARE's are local-scope only. — RBarryYoung, Jun 21 '13 at 19:34

JodyT · Answer 1 · 2013-06-21T20:01:09.977

2

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

edited Jun 21 '13 at 20:01

answered Jun 21 '13 at 19:19

JodyT

4,324
2
19
31

That was part of the question: 'I try to place the declare inside the command' I am finding the syntax errors as I try another strategy, but thanks for pointing them out. – Chloe Jun 21 '13 at 19:47
1

I already explained the syntax error in my Original answer, look at the way you are using the `?` in your code. I missed the `?` you used in the `set @idx` but you should have figured that out yourself with the information I provided you. It's not very nice to downvote people that are trying to help you if the answer is not even incorrect. – JodyT Jun 21 '13 at 20:02

score 1 · Answer 2 · edited May 23 '17 at 10:31

1

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.

edited May 23 '17 at 10:31

Community

1
1

answered Jun 21 '13 at 19:45

Paul Fleming

24,238
8
76
113

1

@Chloe You should think twice before down 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. – Paul Fleming Jun 21 '13 at 19:50
1

@Chloe Also, as with your other question, you're still trying to use variables in the CREATE INDEX statement. You cannot do this. – Paul Fleming Jun 21 '13 at 19:50
That was part of the question: 'I try to place the declare inside the command'. The indices are used for an ETL tool to synchronize databases between different vendors. The link referred to clustered indices, which this is not. I would love to read it more in depth as it seems highly relevant, but I'm under a limited budget so must defer for later. Ok I will try this method again. – Chloe Jun 21 '13 at 19:54
1

@Chloe. The link discusses clustered and non-clustered. I'm aware of your comment about the declare statement. Your attempt was incorrect. The error is due to your misuse of `?`. Re-read then consider un-downvoting. – Paul Fleming Jun 21 '13 at 19:58

How to execute multiline query using sp_MSforeachtable

2 Answers2