Looping across databases

• sql-lover

  SSCoach

  Points: 18530

  More actions

  June 7, 2016 at 12:31 pm

  #327434

  Dynamic SQL is not my forte. Can someone give me a hand and tell me how to loop this TSQL code across all user databases on a given instance?

  --SET NOCOUNT ON;

  DECLARE @indexName VARCHAR(128);

  --Save column list on a variable

  DECLARE @rclist nvarchar(max);

  SET @rclist='LocationID, SaleType, EmployeeID, CheckID';

  DECLARE @rilist nvarchar(max);

  SET @rilist='Amount, DaypartID, RevenueCenterID, TransactionTime';

  IF (EXISTS (SELECT

  OBJECT_NAME(SP.object_id) AS TABLE_NAME

  ,OBJECT_SCHEMA_NAME(ST.object_id) AS SCHEMA_NAME

  FROM sys.partitions SP

  INNER JOIN sys.tables ST

  ON SP.object_id = ST.object_id

  WHERE sp.index_id in (0,1) AND OBJECT_NAME(SP.object_id)='MyTable' ))

  BEGIN

  SELECT

  INDEX_DATA.name AS index_name

  , STUFF(( SELECT ', ' + COLUMN_DATA_KEY_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id

  AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0

  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal

  FOR

  XML PATH('')

  ), 1, 2, '') AS key_column_list

  , STUFF(( SELECT ', ' + COLUMN_DATA_INC_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id

  AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id

  AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id

  AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1

  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal

  FOR

  XML PATH('')

  ), 1, 2, '') AS include_column_list

  INTO #IndexDetails

  FROM sys.indexes INDEX_DATA

  INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id

  INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

  INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id

  WHERE TABLE_DATA.is_ms_shipped = 0

  AND TABLE_DATA.name='MyTable'

  AND INDEX_DATA.type_desc='NONCLUSTERED'

  AND INDEX_DATA.is_primary_key<>1

  --Eliminating any bad Index via CURSOR.

  DECLARE [indexes] CURSOR FOR

  SELECT index_name FROM #IndexDetails WHERE key_column_list<>@rclist AND include_column_list<>@rilist

  OPEN [indexes]

  FETCH NEXT FROM [indexes] INTO @indexName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  EXECUTE ('DROP INDEX [' + @indexName + '] ON MyTable')

  FETCH NEXT FROM [indexes] INTO @indexName

  END

  CLOSE [indexes]

  DEALLOCATE [indexes]

  DROP TABLE #IndexDetails;

  --This is the Index creation part.

  SELECT

  INDEX_DATA.name AS index_name

  , STUFF(( SELECT ', ' + COLUMN_DATA_KEY_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id

  AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0

  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal

  FOR

  XML PATH('')

  ), 1, 2, '') AS key_column_list

  , STUFF(( SELECT ', ' + COLUMN_DATA_INC_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id

  AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id

  AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id

  AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1

  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal

  FOR

  XML PATH('')

  ), 1, 2, '') AS include_column_list

  INTO #NewIndex

  FROM sys.indexes INDEX_DATA

  INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id

  INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

  INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id

  WHERE TABLE_DATA.is_ms_shipped = 0

  AND TABLE_DATA.name='MyTable'

  AND INDEX_DATA.type_desc='NONCLUSTERED'

  IF NOT EXISTS

  (

  SELECT *

  FROM #NewIndex

  WHERE key_column_list=@rclist AND include_column_list=@rilist

  )

  BEGIN

  CREATE NONCLUSTERED INDEX [NCI_MyTable_LocationID_SaleType_EmployeeID_CheckID_INC_MULT] ON [dbo].[MyTable]

  (

  [LocationID] ASC,

  [SaleType] ASC,

  [EmployeeID] ASC,

  [CheckID] ASC

  )

  INCLUDE ( [Amount],

  [DaypartID],

  [RevenueCenterID],[TransactionTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

  END

  DROP TABLE #NewIndex;

  END

  What it does is clean up existing no clustered indexes and deploy a new one based on Index definition. If the new Index already exists, it won't create it, of course.

  The script actually works. I manually ran it via SSMS and works without any issues.

  Any suggestion?

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  June 7, 2016 at 12:44 pm

  #1883709

  Lookup sys.sp_MSforeachdb

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Phil Parkin

  SSC Guru

  Points: 246961

  More actions

  June 7, 2016 at 12:45 pm

  #1883710

  You should find that this[/url], by Aaron Bertrand, allows you to do it.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• sql-lover

  SSCoach

  Points: 18530

  More actions

  June 7, 2016 at 12:48 pm

  #1883711

  drew.allen (6/7/2016)

  ---

  Lookup sys.sp_MSforeachdb

  Drew

  Can you post actual TSQL with it??? Got some syntax errors when trying that.

• sql-lover

  SSCoach

  Points: 18530

  More actions

  June 7, 2016 at 12:50 pm

  #1883713

  Phil Parkin (6/7/2016)

  ---

  You should find that this[/url], by Aaron Bertrand, allows you to do it.

  This is cool! Let me take a look.

• Phil Parkin

  SSC Guru

  Points: 246961

  More actions

  June 7, 2016 at 12:58 pm

  #1883715

  Some pratting about is required to handle the single quotes embedded in your SQL, before it can be passed to an executor proc.

  One way is to replace all single quotes with another character and then do another replace after assignment. I used a tilde in the following code:

  DECLARE @command VARCHAR(MAX);

  SET @command = 'DECLARE @indexName VARCHAR(128);

  --Save column list on a variable

  DECLARE @rclist nvarchar(max);

  SET @rclist=~LocationID, SaleType, EmployeeID, CheckID~;

  DECLARE @rilist nvarchar(max);

  SET @rilist=~Amount, DaypartID, RevenueCenterID, TransactionTime~;

  IF (EXISTS (SELECT

  OBJECT_NAME(SP.object_id) AS TABLE_NAME

  ,OBJECT_SCHEMA_NAME(ST.object_id) AS SCHEMA_NAME

  FROM sys.partitions SP

  INNER JOIN sys.tables ST

  ON SP.object_id = ST.object_id

  WHERE sp.index_id in (0,1) AND OBJECT_NAME(SP.object_id)=~MyTable~ ))

  BEGIN

  SELECT

  INDEX_DATA.name AS index_name

  , STUFF(( SELECT ~, ~ + COLUMN_DATA_KEY_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id

  AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0

  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal

  FOR

  XML PATH(~~)

  ), 1, 2, ~~) AS key_column_list

  , STUFF(( SELECT ~, ~ + COLUMN_DATA_INC_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id

  AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id

  AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id

  AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1

  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal

  FOR

  XML PATH(~~)

  ), 1, 2, ~~) AS include_column_list

  INTO #IndexDetails

  FROM sys.indexes INDEX_DATA

  INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id

  INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

  INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id

  WHERE TABLE_DATA.is_ms_shipped = 0

  AND TABLE_DATA.name=~MyTable~

  AND INDEX_DATA.type_desc=~NONCLUSTERED~

  AND INDEX_DATA.is_primary_key<>1

  --Eliminating any bad Index via CURSOR.

  DECLARE [indexes] CURSOR FOR

  SELECT index_name FROM #IndexDetails WHERE key_column_list<>@rclist AND include_column_list<>@rilist

  OPEN [indexes]

  FETCH NEXT FROM [indexes] INTO @indexName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  EXECUTE (~DROP INDEX [~ + @indexName + ~] ON MyTable~)

  FETCH NEXT FROM [indexes] INTO @indexName

  END

  CLOSE [indexes]

  DEALLOCATE [indexes]

  DROP TABLE #IndexDetails;

  --This is the Index creation part.

  SELECT

  INDEX_DATA.name AS index_name

  , STUFF(( SELECT ~, ~ + COLUMN_DATA_KEY_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id

  AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0

  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal

  FOR

  XML PATH(~~)

  ), 1, 2, ~~) AS key_column_list

  , STUFF(( SELECT ~, ~ + COLUMN_DATA_INC_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id

  AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id

  AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id

  AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1

  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal

  FOR

  XML PATH(~~)

  ), 1, 2, ~~) AS include_column_list

  INTO #NewIndex

  FROM sys.indexes INDEX_DATA

  INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id

  INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

  INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id

  WHERE TABLE_DATA.is_ms_shipped = 0

  AND TABLE_DATA.name=~MyTable~

  AND INDEX_DATA.type_desc=~NONCLUSTERED~

  IF NOT EXISTS

  (

  SELECT *

  FROM #NewIndex

  WHERE key_column_list=@rclist AND include_column_list=@rilist

  )

  BEGIN

  CREATE NONCLUSTERED INDEX [NCI_MyTable_LocationID_SaleType_EmployeeID_CheckID_INC_MULT] ON [dbo].[MyTable]

  (

  [LocationID] ASC,

  [SaleType] ASC,

  [EmployeeID] ASC,

  [CheckID] ASC

  )

  INCLUDE ( [Amount],

  [DaypartID],

  [RevenueCenterID],[TransactionTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

  END

  DROP TABLE #NewIndex;

  END';

  SET @command = REPLACE(@command, '~', '''');

  SELECT @command;

  @Command is now in the correct form ...

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• sql-lover

  SSCoach

  Points: 18530

  More actions

  June 7, 2016 at 1:14 pm

  #1883716

  Phil Parkin (6/7/2016)

  ---

  Some pratting about is required to handle the single quotes embedded in your SQL, before it can be passed to an executor proc.

  One way is to replace all single quotes with another character and then do another replace after assignment. I used a tilde in the following code:

  DECLARE @command VARCHAR(MAX);

  SET @command = 'DECLARE @indexName VARCHAR(128);

  --Save column list on a variable

  DECLARE @rclist nvarchar(max);

  SET @rclist=~LocationID, SaleType, EmployeeID, CheckID~;

  DECLARE @rilist nvarchar(max);

  SET @rilist=~Amount, DaypartID, RevenueCenterID, TransactionTime~;

  IF (EXISTS (SELECT

  OBJECT_NAME(SP.object_id) AS TABLE_NAME

  ,OBJECT_SCHEMA_NAME(ST.object_id) AS SCHEMA_NAME

  FROM sys.partitions SP

  INNER JOIN sys.tables ST

  ON SP.object_id = ST.object_id

  WHERE sp.index_id in (0,1) AND OBJECT_NAME(SP.object_id)=~MyTable~ ))

  BEGIN

  SELECT

  INDEX_DATA.name AS index_name

  , STUFF(( SELECT ~, ~ + COLUMN_DATA_KEY_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id

  AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0

  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal

  FOR

  XML PATH(~~)

  ), 1, 2, ~~) AS key_column_list

  , STUFF(( SELECT ~, ~ + COLUMN_DATA_INC_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id

  AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id

  AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id

  AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1

  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal

  FOR

  XML PATH(~~)

  ), 1, 2, ~~) AS include_column_list

  INTO #IndexDetails

  FROM sys.indexes INDEX_DATA

  INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id

  INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

  INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id

  WHERE TABLE_DATA.is_ms_shipped = 0

  AND TABLE_DATA.name=~MyTable~

  AND INDEX_DATA.type_desc=~NONCLUSTERED~

  AND INDEX_DATA.is_primary_key<>1

  --Eliminating any bad Index via CURSOR.

  DECLARE [indexes] CURSOR FOR

  SELECT index_name FROM #IndexDetails WHERE key_column_list<>@rclist AND include_column_list<>@rilist

  OPEN [indexes]

  FETCH NEXT FROM [indexes] INTO @indexName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  EXECUTE (~DROP INDEX [~ + @indexName + ~] ON MyTable~)

  FETCH NEXT FROM [indexes] INTO @indexName

  END

  CLOSE [indexes]

  DEALLOCATE [indexes]

  DROP TABLE #IndexDetails;

  --This is the Index creation part.

  SELECT

  INDEX_DATA.name AS index_name

  , STUFF(( SELECT ~, ~ + COLUMN_DATA_KEY_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_KEY_COLS ON T.object_id = INDEX_DATA_KEY_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_KEY_COLS ON T.object_id = COLUMN_DATA_KEY_COLS.object_id

  AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id

  AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0

  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal

  FOR

  XML PATH(~~)

  ), 1, 2, ~~) AS key_column_list

  , STUFF(( SELECT ~, ~ + COLUMN_DATA_INC_COLS.name

  FROM sys.tables AS T

  INNER JOIN sys.indexes INDEX_DATA_INC_COLS ON T.object_id = INDEX_DATA_INC_COLS.object_id

  INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id

  AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id

  INNER JOIN sys.columns COLUMN_DATA_INC_COLS ON T.object_id = COLUMN_DATA_INC_COLS.object_id

  AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id

  WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id

  AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id

  AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1

  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal

  FOR

  XML PATH(~~)

  ), 1, 2, ~~) AS include_column_list

  INTO #NewIndex

  FROM sys.indexes INDEX_DATA

  INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id

  INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

  INNER JOIN sys.dm_db_partition_stats AS s ON s.[object_id] = INDEX_DATA.[object_id] AND s.index_id = INDEX_DATA.index_id

  WHERE TABLE_DATA.is_ms_shipped = 0

  AND TABLE_DATA.name=~MyTable~

  AND INDEX_DATA.type_desc=~NONCLUSTERED~

  IF NOT EXISTS

  (

  SELECT *

  FROM #NewIndex

  WHERE key_column_list=@rclist AND include_column_list=@rilist

  )

  BEGIN

  CREATE NONCLUSTERED INDEX [NCI_MyTable_LocationID_SaleType_EmployeeID_CheckID_INC_MULT] ON [dbo].[MyTable]

  (

  [LocationID] ASC,

  [SaleType] ASC,

  [EmployeeID] ASC,

  [CheckID] ASC

  )

  INCLUDE ( [Amount],

  [DaypartID],

  [RevenueCenterID],[TransactionTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

  END

  DROP TABLE #NewIndex;

  END';

  SET @command = REPLACE(@command, '~', '''');

  SELECT @command;

  @Command is now in the correct form ...

  That's cool, Phil. So I guess that's what I need in order to use sp_MSforeachdb?

• Phil Parkin

  SSC Guru

  Points: 246961

  More actions

  June 7, 2016 at 1:37 pm

  #1883724

  Yes, but ...

  I'd suggest trying out a couple of non-destructive queries first ... and definitely in a test environment ... just to get familiar with it.

  Note the comments about sp_msForeachDB sometimes missing databases & keep an eye on that. The Aaron Bertrand link I posted avoids this problem.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin