<div>
<pre><code class="code-1 nolinks" id="code-20-43307641-1">DECLARE @cmd nvarchar(2000)
SET @cmd = N'
USE [?];
IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'') 
&nbsp; CREATE NONCLUSTERED INDEX IX_cases_bActive_state_barcode
&nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([bActive],[state])
&nbsp; &nbsp; &nbsp;INCLUDE ([barcode])'
EXEC sp_MSforeachdb @cmd
</code></pre>Don't forget to exclude master, tempdb, model, msdb...
</div>

<div>
Of course, you may add check for table existence: <pre><code class="code-10 nolinks" id="code-20-43307644-1">DECLARE @cmd nvarchar(2000)
SET @cmd = N'
USE [?];
IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'') 
&nbsp; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'')
&nbsp; CREATE NONCLUSTERED INDEX IX_cases_bActive_state_barcode
&nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([bActive],[state])
&nbsp; &nbsp; &nbsp;INCLUDE ([barcode])
&nbsp; ELSE PRINT DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd
</code></pre>Additional check for index existence should be easy.
</div>

<div>
Did you test this index? 
 
An index over a Boolean is normally not very useful, as it is not that selective. Thus it is often ignored by the query optimizer.
</div>

<div>
Yes, this is true for the index on single boolean column obviously. Of course, when there is just a few rows with one boolean value then it can help significantly. This index is over boolean + char &nbsp;so the optimizer should use it whenever the WHERE clause specifies both columns and uses "index friendly" operators. Of course, this is valid until the optimizer decides differently... Another question is "Why we would need all barcodes for given (in)active state"? We cannot know everything and this question is not about the index tuning.
</div>

<div>
anyway to make sure that this script keep going even see error, like the index already exists... Will this make the script above failed and the index creation is not drop in the middle?&nbsp;
</div>

<div>
Additional data is required. 
 
1) Indexes are applied to tables, having no relation to users. 
 
2) If you're talking about having some database setup with a table/user configuration, the you'll select which tables are indexed. 
 
3) If you do have a table/user config, how/when you apply indexes will be determined by you, as only you know when this is appropriate. 
 
Typically this might be done at time table is created, or maybe a CRON job checking number of total rows, or maybe frequency of user login. 
 
Again, only you know if/when to apply indexes. 
 
4) Likely all this is overkill, as indexes take up near zero space for empty tables + provide massive speed increases as table row count increases, so simple approach... 
 
a) When new table is created, add all indexes. 
 
b) For existing tables, add indexes running at low CPU resource priority, so nice -19 on Linux or your OS equivalent. 
 
5) You asked, &quot;so all user database on a SQL instance, any script for it so that I can save a lot of time to apply the index (es) to nearly all user database (except some I don't want)?&quot; 
 
No such thing. 
 
Adding an index... whether at table creation time or via a script at some future point in time... each approach takes roughly the same CPU cycles + IOPS (I/O operations). 
 
There is no way to somehow... arrange for less CPU/IOPS resource usage.
</div>

Senior Database Architecture 

marrowyung

<div>
<blockquote>You may exclude the database where index already exists or you may add a check for the index existence to the command as proposed earlier, e.g. </blockquote> assume I don't know. what is the way to make it keep going even there are errors ? 
</div>

<div>
David, <blockquote>No such thing. Adding an index... whether at table creation time or via a script at some future point in time... each approach takes roughly the same CPU cycles + IOPS (I/O operations). There is no way to somehow... arrange for less CPU/IOPS resource usage. </blockquote>what I meant is, as the table schema is all the same, I will therefore apply the same to all user database, it is just time saving, not less CPU cycle ! I have to apply the same index to 500 user database! if a script can do it, I prefer script! 
</div>

<div>
<a href="https://www.experts-exchange.com/members/pcelba.html" rel="ugc">pcelba</a> , what if I have more than 1 x index want to create on all user database, e.g. <pre><code class="code-1 nolinks" id="code-20-43308120-1">CREATE NONCLUSTERED INDEX IX_attachment_ty_case_id
ON [dbo].[attachment] ([ty])
INCLUDE ([case_id])
GO

CREATE NONCLUSTERED INDEX IX_credit_card_payments_pid_cc_rid_state_validflag
ON [dbo].[credit_card_payments] ([pid])
INCLUDE ([cc_rid],[state],[validflag])
GO

</code></pre> and I just do this: <pre><code class="code-10 nolinks" id="code-20-43308120-2">DECLARE @cmd nvarchar(2000)
SET @cmd = N'
USE [?];
IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'')&nbsp;&nbsp; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'')
&nbsp; &nbsp; IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_bActive_state_barcode'')
&nbsp; &nbsp; &nbsp; PRINT ''Err '' + DB_NAME() + '' - index already exists''
&nbsp; &nbsp; ELSE
&nbsp; &nbsp; &nbsp; IF 3 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''bActive'', ''state'', ''barcode'') AND object_id = OBJECT_ID(''dbo.cases''))
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BEGIN
&nbsp;CREATE NONCLUSTERED INDEX IX_attachment_ty_case_id
ON [dbo].[attachment] ([ty])
INCLUDE ([case_id])
GO

CREATE NONCLUSTERED INDEX IX_credit_card_payments_pid_cc_rid_state_validflag
ON [dbo].[credit_card_payments] ([pid])
INCLUDE ([cc_rid],[state],[validflag])
GO


&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PRINT ''OK '' + DB_NAME() + '' - index created''
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END
&nbsp; &nbsp; &nbsp; ELSE PRINT ''Err '' + DB_NAME() + '' - one or more columns do not exist''
&nbsp; ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd



</code></pre>? in this case this condition is not going to work? <pre><code class="code-1 nolinks" id="code-20-43308120-3">IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'')


?
</code></pre> and I don't care the table is ''case'' or not, &nbsp;this one should be: <pre><code class="code-1 nolinks" id="code-20-43308120-4">IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'') AND&nbsp; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'')
</code></pre> ? and this one: <pre><code class="code-1 nolinks" id="code-20-43308120-5">IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_bActive_state_barcode'')
</code></pre> I should change it to : <pre><code class="code-1 nolinks" id="code-20-43308120-6">IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''&lt;index name1&gt;'' and name =''&lt;index name2&gt;'')
</code></pre> ? 
</div>

<div>
<blockquote>Important thing is You have to understand what these scripts are doing otherwise you are on a good way to damage your data.</blockquote> That's why I read line by line and post question here after reading your script! I am still deploying the same indexes script to 500 user database, very time consuming <blockquote>&nbsp;You have to change table name, column names, and the CREATE INDEX command.&nbsp;</blockquote>if I have to create separate scripts each one for a different index, right? 
</div>

<div>
 <blockquote>IF 3 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''bActive'', ''state'', ''barcode'') AND object_id = OBJECT_ID(''dbo.cases'')) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BEGIN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BEGIN TRY &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;/* Create the index */ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CREATE NONCLUSTERED INDEX IX_cases_bActive_state_barcode &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([bActive],[state]) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INCLUDE ([barcode]) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PRINT ''OK '' + DB_NAME() + '' - index created'' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END TRY &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BEGIN CATCH &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;/* Process errors */ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE() &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END CATCH &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END</blockquote> this make sure that when script is adding index, and the index of the same name exists, this script will print out error with what DB has this error, and it will skip another DB ?
</div>

<div>
by the way, any way to modify the script to add the DB name current processing against in the script ? now I modify the script so that it only print out the index create script but not executes it: <pre><code class="code-10 nolinks" id="code-20-43308159-1">
DECLARE @cmd nvarchar(2000)
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
-- IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'') 
&nbsp; IF DB_NAME() NOT IN (''util'') 
&nbsp; /* Check for table existence */
&nbsp; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'')
&nbsp; &nbsp; /* Check for index existence */
&nbsp; &nbsp; IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_client_id_bActive_state_subscriber_pan_num'')
&nbsp; &nbsp; &nbsp; PRINT ''Err '' + DB_NAME() + '' - index already exists''
&nbsp; &nbsp; ELSE
&nbsp; &nbsp; &nbsp; /* Check for necessary columns existence */
&nbsp; &nbsp; &nbsp; IF 3 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''bActive'', ''state'', ''barcode'') AND object_id = OBJECT_ID(''dbo.cases''))
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BEGIN
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; /* Create the index */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PRINT "CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num
ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num])
INCLUDE ([patient_first_name],[patient_last_name])
GO
"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PRINT ''OK '' + DB_NAME() + '' - index created''
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BEGIN CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; /* Process errors */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END
&nbsp; &nbsp; &nbsp; ELSE PRINT ''Err '' + DB_NAME() + '' - one or more columns do not exist''
&nbsp; ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd

</code></pre> I planned add comment on top of: &nbsp;PRINT "CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num]) INCLUDE ([patient_first_name],[patient_last_name]) GO so that it also print out the current user database name, any idea? 
</div>

<div>
Answers to questions in previous posts: <a href="#a43308151" rel="ugc" class="scroll">Q1</a> - Yes, create a separate script for each index <a href="#a43308154" rel="ugc" class="scroll">Q2</a> - No, this does not make sure the index already exists. It checks for necessary columns existence (and the comment in the script is stating it already) <a href="#a43308159" rel="ugc" class="scroll">Q3</a> - The DB name is printed right below your PRINT command. BTW, to use double quotes to&nbsp;
</div>

<div>
<blockquote>/* Create the index */ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PRINT "CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num]) INCLUDE ([patient_first_name],[patient_last_name]) GO " </blockquote> actually I only has to worry about this part as only this part do creation and can kill something? but as it is only create index , it only CAN CREATE or FAIL TO CREATE, right? not a big deal !
</div>

<div>
<a href="https://www.experts-exchange.com/members/pcelba.html" rel="ugc">pcelba</a> , <blockquote><a href="https://www.experts-exchange.com/questions/29219171/how-to-apply-the-same-index-to-all-user-database-except-some.html?anchorAnswerId=43308159#a43308159" rel="ugc">Q3</a> - The DB name is printed right below your PRINT command. BTW, to use double quotes to &nbsp;</blockquote>DB_NAME() is the one which shows the existing user database the script is processing, right? 
</div>

<div>
and here is the version I got which seems working well <pre><code class="code-10 nolinks" id="code-20-43308514-1">DECLARE @cmd nvarchar(2000)
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
&nbsp; IF DB_NAME() NOT IN (''util'',''master'',''tempdb'',''model'',''msdb'') 
&nbsp; /* Check for table existence */
&nbsp; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'' )
&nbsp; &nbsp; /* Check for index existence */
&nbsp; &nbsp; IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_client_id_bActive_state_subscriber_pan_num'')
&nbsp; &nbsp; &nbsp; PRINT ''Err '' + DB_NAME() + '' - index already exists''
&nbsp; &nbsp; ELSE
&nbsp; &nbsp; &nbsp; /* Check for necessary columns existence */
&nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp;BEGIN
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; BEGIN TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Create the index */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT "CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INCLUDE ([patient_first_name],[patient_last_name])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GO
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT ''OK '' + DB_NAME() + '' - index created''
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Process errors */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; END CATCH
&nbsp; &nbsp; &nbsp; &nbsp;END
&nbsp; &nbsp;
&nbsp; ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd
</code></pre> for this I don't check for column as in this system, all user database the same schema so I don't do that . and I am testing this script and one thing I am don't think the logic is good is: IF 1 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''case_item_id'') AND object_id = OBJECT_ID(''dbo.case_item_design'')) this is counting the number of column of that table !! how about matching exactly with the column name , or error message still there by: &nbsp;BEGIN CATCH &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; /* Process errors */ &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE() &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END CATCH AFTER I remove the print statement in the create index statement, when runs, error rise: <pre><code class="code-10 nolinks" id="code-20-43308514-2">DECLARE @cmd nvarchar(2000)
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
&nbsp; &nbsp;/* exclude database we do not want index to be create */
&nbsp; IF DB_NAME() NOT IN (''util'',''master'',''tempdb'',''model'',''msdb'') 
&nbsp; /* Check for table existence */
&nbsp; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'' )
&nbsp; &nbsp; &nbsp;&nbsp;/* Check for index existence */
&nbsp; &nbsp; &nbsp;&nbsp;IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_client_id_bActive_state_subscriber_pan_num'')
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT ''Err '' + DB_NAME() + '' - index already exists''
&nbsp; &nbsp; &nbsp;&nbsp;ELSE
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Check for necessary columns existence */
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; -- IF 1 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''case_item_id'') AND object_id = OBJECT_ID(''dbo.cases''))
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;BEGIN
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; BEGIN TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Create the index */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INCLUDE ([patient_first_name],[patient_last_name])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GO;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT ''OK '' + DB_NAME() + '' - index created''
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Process errors */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; END CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;-- ELSE PRINT ''Err '' + DB_NAME() + '' - one or more columns do not exist''
&nbsp; ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd


</code></pre> message: <pre><code class="code-10 nolinks" id="code-20-43308514-3">Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 21
</code></pre> any reason ? I think create index statement can't run inside begin try loop ? should I add one more line like declare @sql nvarchar(1000), then &nbsp;set @sql='CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num]) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INCLUDE ([patient_first_name],[patient_last_name]) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GO'; then exec (@sql); ? e.g. <pre><code class="code-10 nolinks" id="code-20-43308514-4">
DECLARE @cmd nvarchar(2000)
, @sql nvarchar(1000) &nbsp;--
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
&nbsp; &nbsp;/* exclude database we do not want index to be create */
&nbsp; IF DB_NAME() NOT IN (''util'',''master'',''tempdb'',''model'',''msdb'') 
&nbsp; /* Check for table existence */
&nbsp; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'' )
&nbsp; &nbsp; &nbsp;&nbsp;/* Check for index existence */
&nbsp; &nbsp; &nbsp;&nbsp;IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_client_id_bActive_state_subscriber_pan_num'')
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT ''Err '' + DB_NAME() + '' - index already exists''
&nbsp; &nbsp; &nbsp;&nbsp;ELSE
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Check for necessary columns existence */
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; -- IF 1 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''case_item_id'') AND object_id = OBJECT_ID(''dbo.cases''))
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;BEGIN
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; BEGIN TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Create the index */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; SET @sql=N''CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INCLUDE ([patient_first_name],[patient_last_name]) go;''

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exec sp_executesql @sql;

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;/* CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INCLUDE ([patient_first_name],[patient_last_name])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GO */

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT ''OK '' + DB_NAME() + '' - index created''
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Process errors */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; END CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;-- ELSE PRINT ''Err '' + DB_NAME() + '' - one or more columns do not exist''
&nbsp; ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd



</code></pre> 
</div>

<div>
All the GO commands should be removed from your script. Why did you add them? The command IF 1 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''case_item_id'') AND object_id = OBJECT_ID(''dbo.case_item_design'')) counts the number of columns having name IN defined list in the given table and compares it to number 1... so the program flow continues when columns exist in the table. You may remove this IF and TRY - CATCH block will handle missing columns sufficiently.
</div>

<div>
<blockquote>
You may remove this IF and TRY - CATCH block will handle missing columns sufficiently.
</blockquote>
Which is true for the table tests also. 
 
And the index test, testing for the name makes no sense. When using index existence test, then test for any index having the same columns in the correct order.
</div>

<div>
<a href="https://www.experts-exchange.com/members/ste5an.html" rel="ugc">ste5an</a>,<blockquote>When using index existence test, then test for any index having the same columns in the correct order.</blockquote> so only test the index name existed already is not enough, but also check number of field if it is the same is very enough ? <blockquote>test for any index having the same columns in the correct order.</blockquote> how ? <a href="https://www.experts-exchange.com/members/pcelba.html" rel="ugc">pcelba</a> , <blockquote>All the GO commands should be removed from your script. Why did you add them? </blockquote>the GO come with original index script, so I copy over there. so just : <pre><code class="code-10 nolinks" id="code-20-43308546-1">BEGIN TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Create the index */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INCLUDE ([patient_first_name],[patient_last_name])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GO 

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT ''OK '' + DB_NAME() + '' - index created''
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END TRY
</code></pre> ? <blockquote>counts the number of columns having name IN defined list in the given table and compares it to number 1... so the program flow continues when columns exist in the table </blockquote> so the number is not always 1, right ? I just think this one means only 1 x columns as the table column, no ? and you just set it 1 as the select count (*) returns a number and it should be ALWAYS true = 1 ?
</div>

<div>
so later on if need to rollback we just need to replace create index with drop index command ? DROP INDEX [IX_case_item_design_case_item_id] ON [dbo].[case_item_design] ? <pre><code class="code-10 nolinks" id="code-20-43308550-1">
DECLARE @cmd nvarchar(2000)
, @sql nvarchar(1000) &nbsp;--
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
&nbsp; &nbsp;/* exclude database we do not want index to be create */
&nbsp; IF DB_NAME() NOT IN (''util'',''master'',''tempdb'',''model'',''msdb'') 
&nbsp; /* Check for table existence */
&nbsp; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''case_item_design'' AND type = ''U'' )
&nbsp; &nbsp; &nbsp;&nbsp;/* Check for index existence */
&nbsp; &nbsp; &nbsp;&nbsp;IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_case_item_design_case_item_id'')
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT ''Err '' + DB_NAME() + '' - index already exists''
&nbsp; &nbsp; &nbsp;&nbsp;ELSE
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Check for necessary columns existence */
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; -- IF 1 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''case_item_id'') AND object_id = OBJECT_ID(''dbo.cases''))
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;BEGIN
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; BEGIN TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Create the index */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; /* &nbsp; SET @sql=N''CREATE NONCLUSTERED INDEX IX_cases_client_id_bActive_state_subscriber_pan_num
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ON [dbo].[cases] ([client_id],[bActive],[state],[subscriber_pan_num])
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INCLUDE ([patient_first_name],[patient_last_name]) go;''

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exec sp_executesql @sql; */


&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DROP INDEX [IX_case_item_design_case_item_id] ON [dbo].[case_item_design]


&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; PRINT ''OK '' + DB_NAME() + '' - index created''
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END TRY
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; /* Process errors */
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; END CATCH
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END
&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;-- ELSE PRINT ''Err '' + DB_NAME() + '' - one or more columns do not exist''
&nbsp; ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd
</code></pre> ? 
</div>

<div>
Just a comment: 
 
The patients name should be properly normalized. 
When [state] denotes a regional state, then it should be properly normalized. Otherwise having an active flag and an status column is redundent.
</div>

<div>
<blockquote>Your DROP INDEX attempt cannot work in the given script context. Try to explain why.</blockquote> not sure, may have to test to run that and then see the error. <blockquote>You should not copy everything from any script you obtain anywhere... You should know what the commands are used for. &nbsp;This will avoid data damage or just a confusion. &nbsp;Just imagine the DROP TABLE command placed in the middle of some longer script etc.</blockquote> that's why I keep asking. and now I already using that script to run the index creation, tks. I remark that. <blockquote> <img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAApIAAAAlCAYAAADx2AmCAAASyElEQVR4Ae1dC7YqOQh0X27K/bgXV/L20nOAkFQR0tr3M9cPc8480wkQKEi6TKv3tNV/hUAhUAgUAoVAIVAIFAKFwBcQOH1Bp1QKgUKgECgECoFCoBAoBAqBrYhkFUEhUAgUAoVAIVAIFAKFwJcQKCLZYPt3PW/n678vgVhKX0Xgtl1Ol+1G6v+26/m8xVTcLqfKD+H0vYvC83v4fao27pNSQ6dLW723y3aa1vKnonQ0btkHT5tBKfvf2OsmjM/X7dXuUlIzvU6OQvMn8pyDP3HhxSb9IyIZEvXvup1Pp+1E/0eCMZDVwjyNxTZGvtqShYzkxfzr/vhmuW2bLmzyc2ym6le20HWT3Y/PY/I5ldSmes0O+ERRRyyjP5NNjFsDzG8Ioge2HAd2Y+Q1xuNxyasTdpXpBhrm5+t2vZy361VuTOCbxAXzU8xbyFfLj5m2TRrnxzi6nXu4dFwXdXlvvE/0HA3Jn+fhOTxyL0YNeY+8Wj1F7EFW8Yd6acpcY0kthBs41QkRo7zGnhNDRK61tb4Hfr4+o/9EXCYzvE9SDYX9YVL9g45YM+Tv/+qP1V3Eerhg47ZfWZ35tsg+y9hc48NOa/le5ka6gNdwtIHz+1oL96vl3tuNLxu8Bmcxrblv2FeLB+tvf07DaZ2vOYZP73kiIhmLO0uNL8gbvWvLJA/1hSKMRSYLwdekjvlFmEQXTLYgdGGPTTyoGTklvXnDWNpGYzoP4yh6viDUBpIz0VUdfzfs14mvhFFbaOdzIJ35AswxM9kJSidk04DknmMboefz2jhvkpuTTrD/OC7n7QynBWP+tvmez9uZyAdKPFdbcuJ18VyeOZZ88qI11Emfeww1cYBIQurdkFDVaU/ROfu6nMdB+fmbYQ+y2KReeU1pfw6Q7RUdD8tTryHBH8aeERCJrfv7VA7yHiV+egpw/xaXH4lBZS6XZC+yec6yb1OueH7dD2ncav+rJ85qzwP6Ldzp/vTdSV58rX83/C/ovxiR9Ah/NtGy8PDoPV77rPK6NzYvwKYZNnG0Z0QuIW4klN9cWYQ3Ax7TIx099c3WM/m98pUWquMfCb3388OXFLPpxm+6ssGlJ5K7m2g+r2Ew40LxNuL6MC6Eg6Msc5y3601O1he5bPP4iZfMN+OCcTQ8/PQ7c1CnZzm8UWqcrh9ImMztsoSH2mTMRPZyw3mMfKj/dPoryqJ72W4Yb3pT2jlZn2rD8L1cwiMyzMWkY7nR2Dp2HJdJ+L+IfeujtZCMu2p8Fb3LrZ2iWpyOtYmaH14LOHYM67GuM1vkFsUyak9z2PEZ/aTbLqKsYNt9F/wxz5h/Iqvr2P1NrcUCBJdsjbrNfKS+Xh88p9jvISsu4/St9+sbC/EBdWVtz+uA5kwvTGfYbji3tXO+XuHRto25LGEstntM6URt/Ynf85y2NiV2i8nnGP1mc94PpP9O/Ycc4f3U1yDuF71u2r6O8lwHkCt1A59ezvuQ1s4IbAWSrU2QUx99r7xcpzeVS0M1oAi8KZHExT42CdpAegEkC6RtLljsLh43U++X13wBtsW/IBi+yNBO1l7admFdyAsSIzIa02Icx7Dttl2/3ygAM5X3TR/6QTfFbDVP38DBQGuuscrnNbW4aQbZpR8Bsy4X7bW8y6a0zIHNCftWcy18rkz1Dct1rIwLYyvzWI5VH2uO8mQ3K6/vubY4RrsBeI5NV9YS6fe5THeceNg1yU5AcEx+0+pi4rvUXs+BwwdvAAE7tMY4clwo53O6nzI2YztiZt1wpX6CLGEvORpYWs2M669g3XFaYKDerbBTnXGz5pgxrrBucGhqB5xlDnVyL3bRGTgMk4mtVG5o9JbE3PcsrnmViXgRRhbvOIXz64AV2O/zTg3T7XmiecZ66uOTPnSoz4t9XMQg5r11zfsDYzzr2fyrfqvhgYsTU19LNheMa/wj11RzuzlhP3VeBw3iBrSWTfXJdcOc7q/7j0ZsfTKnwL0QZT+p/UREMiTHk5xmwxZmluhUfLczFKfLanE1n8CXuZDGglguNF04HJ/7TovI505el7ZdVud4bINxlf6Km9PKDi1Uxn/EwP1uf4x7zw7pHiJzi3zAYZtX3436u8pIbHo/bGhiYmnTT3sapoALbULtnbqWCOKI7vkpBtSRDXPtoV1t9xjIGFyI/qi/MWB48HScG8mJ1+BcW+wXyuocETPaiGefMP+PxdVuri0A0TFf0XaIEdcr5FprogNhcVGddCIw15DjY7jO44NkDORTfPSUNtRdV/kG1jEPjfyy320iqF/pmXLScMD+7qI22E8eC1eJX0GiXaJNa0++J7amesyNT2s76mkt9toQI1hT2LYJJvmA6coNtmvYc5yIw9qKjYhstuZl1HzutmldyjjOg7LYv7M3J7nQWZMv0yhWraYm3IKfWHMrWU3TYn7FZW9MBfgfnAfbJoXYsF5d5Qj8AJE00PvmLBnXBebEKSv6kKip4HNnR2/QHwNfaPEimg3YuB+9Y9FHWS3IfmOCUcUjJ3l79sDC+rTThXbmUJG9cRzDttuWV+nvsQX8e/5Cf9PPYlxihXPGNvmAg/m8JsH51XmRoK3iFWUcw3aPN+Ci/XmefZPXddJxhNNMuomZ5+arrKNsDTnRzcY4ZkdK8uA3GWzPuWB9lFVbMQ+Ih96s2Ce1Dzfsu3ER9pZbV+++RKzJB48Y8VWj9AhxSEkr1JDmG+MI46zMVxGfmFv11fdHe53ic4vRFsQ5cGRbnmM3oa8az6hNwdH3NCQY3I8WuCZwJLbnegKJndjdDyT/h2KEabQZsOu10+Q01vimoz/25roTFfXFEyUdAdNmNnlBW1kdPY6tYYR1CdNBbVhvnCvM0/3n/lX+Vv1pzQD2E24KHe9FXot7OVnNr7HCfIDIsok+zf5H3JZmaqAh8ANE8itYhkRNC+CezaA/idt4J7ewWeA+YGq8iCZTvoG0m/9cdENjWeh9wQ7Z3tob60I77xK7zJ04djCmmBZyHNuMv9o4X7ZL8mUUst/8ZXs9iP3GcrOY/RmGIi5BdhGv6JPfIU8ydr6Gz4iqrXGzHj5gq9WmF6Lr+CuKtrZiheS3y0hs2U3F5vApTJzjNv/ts6xzLhgzlFVbMQ+E4eyT2mdn1Mw6Lhl2O/IKmLa5b/EUhHywiOVfnpvjGlIqOX0uivIfiSYr81XEh4hk9IGvD2E9zcNu0JXIAo4cW8PpdNkuRDDRAvuJI1N76Ve0Ea/dkvUrIV7actmd16AbseXaiHbmNTTJB0yjhXHNtqIfTqCTJTJM9JZgk615zyG/qeA3rhFv8+t0udAbLI0T3uza1Lx/dHemNdakZX02GxNuYS1hLc6yMFPIJ4yEgw4aSS9wHmyb8DpW9RX4hHOM9M1bOvN7dr4pkTySrFg0stjgxrVT9HEWLchpAd5759oWM+lJH28WS9vghMqETUb6vMhtEWBs7hvOlfmTb0Bu11wwGVlY3B8Imfv78CbsCpEUjP7pNAmH6HGODUQsH8Il+qvXsmkDnjtkEF2S+QZGDW/ZnFZ3EtxAdV7PV9PtenJt/mhMWFOkZznpPiRjkkc3y/62mkHbROKkDtw/wNuNMRD9ZoPd3tZ55RumqNvmkm/PY3f8rKHb0Fx3wVjHLiWvhmXHRLoUF89vMo7q2BY9xAeJJGHlc4xYDmGttoYuujC1KZZsTbZaWtbhgfijX3ItObgT+/DZ5tK0RVtD6H4r5EHXRK8Fx55rdRgFH1on15Lre30MzbnFttQO7BvqF6y3WR96FI9sTqttql9RI/yS+m/juN7j/ihmzMdsXscBcWRfLF6oU63FIU95CWMQeYilxeb5DPVNeskF5TLM6f5OWCZ2qssQeDEiaQXq7wL89bsJp0IWXLSw4J2dF2tfUDAGG68XoPslr+pbtKfvaHBR2kYz6UGVqm26OcEgNCcfgs40DhvaMDP7AxDkN11d1/JzQEiSzOKEr3TrBjY2kzH3uiV28lybv/lYsnmuyKXmxXOL+fGawL6GEQJzZ5Pv+V3kBE0pZt0fwClsen6a4bYRg6WNVscrWT9pdX8m3MUHjIFyKXiDv14XzdieT1Pm27pBP53wEYHfqSetdw+k5d2x8lcbzmoI+1q+e06sTti3FkHEB4mk4+F22im+u3gMa19HXrP26rYIT8Vy1K/mIQo2vIm4g5FUB8ap6bY0zlEPtP9g7EBoNC/oWxx7lHTFPICdbp78FPzcV8t3l/O8YYfqDkwpfrqYbeE68F9EQNOkjhcxJh/b8WXkLdsLnSQOokc58jq951zAEdeF2PM9hdecOT/8a8EEWyMnvg97vXuuRA/W5z1fk1xizOK7+IQxOMz1miPwR0Qyd+bPeleL888c+oSJ5811P+p8E9zXeZHRnZvAi0RQbn4CArVP/nmWJ9L15x5934F3jOn7qLyWhSKSmq/5FOW10via3uq7wAfePVqK+DNerxlx7nVtpDku1ftsCNQ++bcZec8303X697dV9ROzF5FsKNrxO/+Q9k8AXDb2EJCN8ZFHQ/b45T0fNbznzWEv6zX2ugg8zz4JjzL98Su+PvoG9SdSMT2K9Uev+Kj8ByZ6txPh/lGDx+4BjyNoe6o/Ro+v73kfeRyd35AsIvkbqJbNQqAQKAQKgUKgECgEPgCBIpIfkOQKsRAoBAqBQqAQKAQKgd9AoIjkb6BaNguBQqAQKAQKgUKgEPgABIpIfkCSK8RCoBAoBAqBQqAQKAR+A4Eikr+BatksBAqBQqAQKAQKgULgAxAoInk4yfhNY/zGrX2LcP2NMJR9r28h48/X6E/6+I9V67fy8Bt5R3878nBySqEQKAQKgUKgECgE/kcEjhPJ/pX98RMH9EsLyfj8lxKEVI1fpVfygT/dIG0nIw0MJSsgI3Omek1mTegcXSZ201+zUTtIgkxP5+wBYxz3SBLP906/nYWxMD5GmDtcAqH8hAV1eD7qtRAoBAqBQqAQKAReDYFjRLL9XhbxgEYcO3HT60ES/U+49XFFCAlYI4SBOA4g2++F0aSizyQPT8WG7l6LiR3/XV3TM/KKsUSyKNdjXOTJTZqe50PyRWIveCHksec3EMVILO1PI3LuXjDkcrkQKAQKgUKgECgEtm07QCSNCHXCgPApwWzkYCKSdirFp1BiaxAwJRsLIrk3xi6cDp50MbHLiKTYJ4I6xbZPJI2I2snt+XrdLvA3Yo1I3rbreXGyG/8u8AIfxwDnQqypn2xY/P5jrZhX0iHCnuvsEcmZOEYy7hHUayFQCBQChUAhUAi8GgKPE8mJREGoOIZtFQmErfc9RiSNcN3/izNKftbHgeCsN4NfSIZdRF6xH9sok7WDrJMzd9GuAwadtJlvg9wZ+UKCiFNGsn27mF0ieO2P2rvNFa6zrUHQVzroy9yWWEacMv41O7Pl6ikECoFCoBAoBAqBv0XgIJFcPZIEsqBEcpyy2YlX1AP5Lf+soxEuI1BOfvagUmLmLG1PsI8ZWesqgfh1MY3H/I8kq8skjZks8Xy74+ILnR4GQkvzPX7ChxhpO87RyGbHROaB+HMdcia5mP1THGmSRK26CoFCoBAoBAqBQuDpEThIJPlkqUeHp5DYbgJKHPppm3QmRHIiNSL3BEQSCObjRDLz+x6RHIQrnQcIXcddG2yXx8ymP77WVyBwSgzpS0Vmi+RpvD3qD308Z7wacflIEUlHol4LgUKgECgECoHXRuBxItk+swc8pEdOxCchknaqhST0USJ574s43QX+LOPo3mkFAgaEEZXwFI/iRKGkLXp8ksrz7Y7/0Ikk+i4uxmt3W/uVyM+kz2Xi69CJI/F6tllEMmJU14VAIVAIFAKFwGsicIBI+iNoJIT+6BO+rZwQSSUOeCIZZPYJWjslIwYrffy4fEWS1mlhYkefhWxK5jfEuyCb2RwxZvUvfNnmBJ8dJHnFBzBtJ7P9M5JhPJI6+4ykEbhBZjMczXOdu+EbbWWxSR/qrGSsX+YFDOszkvtw1WghUAgUAoVAIfBCCBwikhqXkin8DCSTBDt9xHFpN9KHukAMjUQFHXrU3UiQPlI1OVBvbo0vhTyGf0Yk93xw0hzi3ZnMyaM8Kr7c+GROxi43jCvYbWSxP2bGgAOR9I8ATLJk47xdLuf2zXbzpct7flos6LfKwGnlSmcJg/qApJ9xWOrVQCFQCBQChUAhUAg8PQLHieTTh/SbDhYJOoquvknANwVKLANpPmq05AuBQqAQKAQKgULgKRAoInkwDY8/0j1o+C3FjXiPx+uPf+b1LeGooAqBQqAQKAQKgTdD4G2JZPq4vD8ax0etRzMqj6O/o390vheWr8faL5y8cr0QKAQKgUKgELiPwNsSyfuhl0QhUAgUAoVAIVAIFAKFwHcQKCL5HfRKtxAoBAqBQqAQKAQKgQ9GoIjkBye/Qi8ECoFCoBAoBAqBQuA7CPwHdjSKeMd15r4AAAAASUVORK5CYII=" class="fr-fic fr-dib"> </blockquote>
</div>

<div>
The DROP INDEX answer: Your code contains following conditions <pre><code class="code-1 nolinks" id="code-20-43310875-1">IF the index exists &nbsp;PRINT something
ELSE &nbsp;drop the index
</code></pre> 
</div>

<div>
so this is it : &nbsp; &nbsp; &nbsp; IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_case_item_design_case_item_id'') &nbsp;drop index IX_case_item_design_case_item_id ? 
</div>

<div>
but you are saying drop index is not going to work in this way, right? 
</div>

<div>
my task done here. tks and this will works: IF the index exists &nbsp;PRINT something &nbsp;ELSE &nbsp;drop index IX_case_item_design_case_item_id ? 
</div>

<div>
<div class="content wysiwyg-content fr-view">
hi, I am going to apply the indexes, like: <pre><code class="code-1 nolinks" id="code-10-29219171-1">CREATE NONCLUSTERED INDEX IX_cases_bActive_state_barcode
ON [dbo].[cases] ([bActive],[state])
INCLUDE ([barcode])
GO
</code></pre> so all user database on a SQL instance, any script for it so that I can save a lot of time to apply the index (es) to nearly all user database (except some I don't want)? the script must have a list let me exclude some user database I don't want to apply for it. we will have more than 1x indexes I'd like to deploy. please share a script you usually use on that. 
</div>
</div>


hi,
I am going to apply the indexes, like:
CREATE NONCLUSTERED INDEX IX_cases_bActive_state_barcode
ON [dbo].[cases] ([bActive],[state])
INCLUDE ([barcode])
GO
so all user database on a SQL instance, any script for it so that I can save a lot of time to apply the index (es) to nearly all user database (except some I don't want)?
the script must have a list let me exclude some user database I don't want to apply for it.
we will have more than 1x indexes I'd like to deploy.
please share a script you usually use on that.

how to apply the same index to all user database except some

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

A scripting language is a programming language that supports scripts, programs written for a special run-time environment that automate the execution of tasks that could alternatively be executed one-by-one by a human operator. Scripting languages are often interpreted (rather than compiled). Primitives are usually the elementary tasks or API calls, and the language allows them to be combined into more complex programs. Environments that can be automated through scripting include software applications, web pages within a web browser, the shells of operating systems (OS), embedded systems, as well as numerous games. A scripting language can be viewed as a domain-specific language for a particular environment; in the case of scripting an application, this is also known as an extension language.