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

DECLARE @cmd nvarchar(2000)
SET @cmd = N'
USE [?];
IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'') 
  CREATE NONCLUSTERED INDEX IX_cases_bActive_state_barcode
     ON [dbo].[cases] ([bActive],[state])
     INCLUDE ([barcode])'
EXEC sp_MSforeachdb @cmd

Select allOpen in new window

Don't forget to exclude master, tempdb, model, msdb...

Of course, you may add check for table existence:

DECLARE @cmd nvarchar(2000)
SET @cmd = N'
USE [?];
IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'') 
  IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'')
  CREATE NONCLUSTERED INDEX IX_cases_bActive_state_barcode
     ON [dbo].[cases] ([bActive],[state])
     INCLUDE ([barcode])
  ELSE PRINT DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd

Select allOpen in new window

Additional check for index existence should be easy.

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.

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  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.

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? 

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, "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)?"

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.

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.

assume I don't know. what is the way to make it keep going even there are errors ?

David,

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.

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!

pcelba ,

what if I have more than 1 x index want to create on all user database, e.g.

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

Select allOpen in new window

and I just do this:

DECLARE @cmd nvarchar(2000)
SET @cmd = N'
USE [?];
IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'')   IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'')
    IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_bActive_state_barcode'')
      PRINT ''Err '' + DB_NAME() + '' - index already exists''
    ELSE
      IF 3 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''bActive'', ''state'', ''barcode'') AND object_id = OBJECT_ID(''dbo.cases''))
         BEGIN
 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


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

Select allOpen in new window

?

in this case this condition is not going to work?

IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'')


?

Select allOpen in new window

and I don't care the table is ''case'' or not,  this one should be:

IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'') AND  IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'')

Select allOpen in new window

?

and this one:

IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_bActive_state_barcode'')

Select allOpen in new window

I should change it to :

IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''<index name1>'' and name =''<index name2>'')

Select allOpen in new window

?

Important thing is You have to understand what these scripts are doing otherwise you are on a good way to damage your data.

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

 You have to change table name, column names, and the CREATE INDEX command. 

if I have to create separate scripts each one for a different index, right?

IF 3 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''bActive'', ''state'', ''barcode'') AND object_id = OBJECT_ID(''dbo.cases''))
         BEGIN
         BEGIN TRY
         /* Create the index */
         CREATE NONCLUSTERED INDEX IX_cases_bActive_state_barcode
             ON [dbo].[cases] ([bActive],[state])
             INCLUDE ([barcode])
         PRINT ''OK '' + DB_NAME() + '' - index created''
         END TRY
         BEGIN CATCH
         /* Process errors */
         PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
         END CATCH
         END

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 ?

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:

DECLARE @cmd nvarchar(2000)
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
-- IF DB_NAME() NOT IN (''DBname1'', ''DBName2'', ''DBname3'') 
  IF DB_NAME() NOT IN (''util'') 
  /* Check for table existence */
  IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'')
    /* Check for index existence */
    IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_client_id_bActive_state_subscriber_pan_num'')
      PRINT ''Err '' + DB_NAME() + '' - index already exists''
    ELSE
      /* Check for necessary columns existence */
      IF 3 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''bActive'', ''state'', ''barcode'') AND object_id = OBJECT_ID(''dbo.cases''))
         BEGIN
          BEGIN TRY
          /* Create the index */
          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
"
          PRINT ''OK '' + DB_NAME() + '' - index created''
          END TRY
         BEGIN CATCH
          /* Process errors */
          PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
         END CATCH
         END
      ELSE PRINT ''Err '' + DB_NAME() + '' - one or more columns do not exist''
  ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd

Select allOpen in new window

I planned add comment on top of:
 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?

Answers to questions in previous posts:
Q1 - Yes, create a separate script for each index
Q2 - 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)
Q3 - The DB name is printed right below your PRINT command. BTW, to use double quotes to 

/* Create the index */           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 "

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 !

pcelba ,

Q3 - The DB name is printed right below your PRINT command. BTW, to use double quotes to  

DB_NAME() is the one which shows the existing user database the script is processing, right?

and here is the version I got which seems working well

DECLARE @cmd nvarchar(2000)
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
  IF DB_NAME() NOT IN (''util'',''master'',''tempdb'',''model'',''msdb'') 
  /* Check for table existence */
  IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'' )
    /* Check for index existence */
    IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_client_id_bActive_state_subscriber_pan_num'')
      PRINT ''Err '' + DB_NAME() + '' - index already exists''
    ELSE
      /* Check for necessary columns existence */
   
       BEGIN
           BEGIN TRY
              /* Create the index */
              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
               "
              PRINT ''OK '' + DB_NAME() + '' - index created''
            END TRY
          BEGIN CATCH
              /* Process errors */
              PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
           END CATCH
       END
   
  ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd

Select allOpen in new window

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:

 BEGIN CATCH
              /* Process errors */
              PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
           END CATCH

AFTER I remove the print statement in the create index statement, when runs, error rise:

DECLARE @cmd nvarchar(2000)
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
   /* exclude database we do not want index to be create */
  IF DB_NAME() NOT IN (''util'',''master'',''tempdb'',''model'',''msdb'') 
  /* Check for table existence */
  IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'' )
      /* Check for index existence */
      IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_client_id_bActive_state_subscriber_pan_num'')
        PRINT ''Err '' + DB_NAME() + '' - index already exists''
      ELSE
        /* Check for necessary columns existence */
        -- IF 1 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''case_item_id'') AND object_id = OBJECT_ID(''dbo.cases''))
            BEGIN
                 BEGIN TRY
                    /* Create the index */
                   
                     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;
                     
                    PRINT ''OK '' + DB_NAME() + '' - index created''
                  END TRY
                BEGIN CATCH
                    /* Process errors */
                   PRINT ''ERROR WHEN CREATING INDEX in '' + DB_NAME() + '': '' + ERROR_MESSAGE()
                 END CATCH
            END
         -- ELSE PRINT ''Err '' + DB_NAME() + '' - one or more columns do not exist''
  ELSE PRINT ''Err '' + DB_NAME() + '' - table does not exist'''
EXEC sp_MSforeachdb @cmd

Select allOpen in new window

message:

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

Select allOpen in new window

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

 set @sql='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';

then exec (@sql); ?


e.g.

DECLARE @cmd nvarchar(2000)
, @sql nvarchar(1000)  --
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
   /* exclude database we do not want index to be create */
  IF DB_NAME() NOT IN (''util'',''master'',''tempdb'',''model'',''msdb'') 
  /* Check for table existence */
  IF EXISTS (SELECT * FROM sys.tables WHERE name = ''cases'' AND type = ''U'' )
      /* Check for index existence */
      IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_cases_client_id_bActive_state_subscriber_pan_num'')
        PRINT ''Err '' + DB_NAME() + '' - index already exists''
      ELSE
        /* Check for necessary columns existence */
        -- IF 1 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''case_item_id'') AND object_id = OBJECT_ID(''dbo.cases''))
            BEGIN
                 BEGIN TRY
                    /* Create the index */
                   
                 SET @sql=N''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;''

                     exec sp_executesql @sql;

                     /* 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 */

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

Select allOpen in new window

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.

You may remove this IF and TRY - CATCH block will handle missing columns sufficiently.

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.

ste5an,

When using index existence test, then test for any index having the same columns in the correct order.

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 ?

test for any index having the same columns in the correct order.

how
?

pcelba ,

All the GO commands should be removed from your script. Why did you add them?

the GO come with original index script, so I copy over there.

so just :

BEGIN TRY
                    /* Create the index */
                   
               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 

                     
                    PRINT ''OK '' + DB_NAME() + '' - index created''
                  END TRY

Select allOpen in new window

?

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

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 ?

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

DECLARE @cmd nvarchar(2000)
, @sql nvarchar(1000)  --
/* Create index on all databases */
SET @cmd = N'
USE [?];
/* Exclude unwanted databases */
   /* exclude database we do not want index to be create */
  IF DB_NAME() NOT IN (''util'',''master'',''tempdb'',''model'',''msdb'') 
  /* Check for table existence */
  IF EXISTS (SELECT * FROM sys.tables WHERE name = ''case_item_design'' AND type = ''U'' )
      /* Check for index existence */
      IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_case_item_design_case_item_id'')
        PRINT ''Err '' + DB_NAME() + '' - index already exists''
      ELSE
        /* Check for necessary columns existence */
        -- IF 1 = (SELECT COUNT(*) FROM sys.columns WHERE name IN (''case_item_id'') AND object_id = OBJECT_ID(''dbo.cases''))
            BEGIN
                 BEGIN TRY
                    /* Create the index */
                   
               /*   SET @sql=N''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;''

                     exec sp_executesql @sql; */


          DROP INDEX [IX_case_item_design_case_item_id] ON [dbo].[case_item_design]


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

Select allOpen in new window

?

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.

Your DROP INDEX attempt cannot work in the given script context. Try to explain why.

not sure, may have to test to run that and then see the error.

You should not copy everything from any script you obtain anywhere... You should know what the commands are used for.  This will avoid data damage or just a confusion.  Just imagine the DROP TABLE command placed in the middle of some longer script etc.

that's why I keep asking.

and now I already using that script to run the index creation, tks.

I remark that.

tks.

The DROP INDEX answer:
Your code contains following conditions

IF the index exists  PRINT something
ELSE  drop the index

Select allOpen in new window

so this is it :

      IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''IX_case_item_design_case_item_id'')
 drop index IX_case_item_design_case_item_id
?

Yes

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

No

my task done here. tks

and this will works:

IF the index exists  PRINT something
 ELSE  drop index IX_case_item_design_case_item_id

?

tks.