Dynamic creating indexes

• SQLPirate

  SSCrazy

  Points: 2880

  More actions

  September 11, 2018 at 1:18 pm

  #2005315

  At the bottom of this page there's a script posted by shahmm that will give you your IF NOT EXISTS...CREATE scripts for all of the indexes. You can strip the GO from the script so the statements go into your stored proc cleanly.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 11, 2018 at 2:34 pm

  #2005336

  Here is dynamic SQL that will generate the script you need.  It will be initially displayed in a column in grid mode, left clink on the link and it will take it to an xml display.  You can then copy paste the output to a query window and put it in a stored procedure that you store in the target database.  This script must also be run in the target database when all the indexes exist.  You will need to test it in a test environment to ensure it does what you expect.

  
DECLARE @IndexCreateSQLTemplate NVARCHAR(MAX)
   ,@IndexCreateSQL NVARCHAR(MAX)
   ,@SQLCmd NVARCHAR(MAX);

  SET @IndexCreateSQLTemplate = N'

  SET @IndexCreateSQL = N''
IF NOT EXISTS(SELECT 1
     FROM [sys].[indexes] AS [idx]
     WHERE [idx].[name] = ''''<IndexName>'''' AND [idx].[object_id] = OBJECT_ID(''''<TableName>'''',''''U''''))
CREATE <UNIQUE> <IndexType> INDEX <IndexName>
ON <TableName> (<indexColumns> )
<IncludeClause>
ON [DEFAULT];
'';

  EXEC [sys].[sp_executesql] @stmt = @IndexCreateSQL;

  ';

  SET @SQLCmd = N'DECLARE @IndexCreateSQL NVARCHAR(MAX);'

  DECLARE @TableName  NVARCHAR(256)
   ,@IndexName  NVARCHAR(128)
   ,@TypeDesc   NVARCHAR(128)
   ,@UniqueType  NVARCHAR(128)
   ,@ObjectId   INT
   ,@IndexId   INT
   ,@IndexColumns NVARCHAR(MAX)
   ,@IncludeColumns NVARCHAR(MAX);

  DECLARE IndexCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([tab].[object_id])) + '.' + QUOTENAME([tab].[name]) AS TableName
, QUOTENAME([idx].[name]) AS IndexName
, [idx].[type_desc]
, CASE [idx].[is_unique] WHEN 1 THEN N'UNIQUE' ELSE N'' END AS UniqueType
, [tab].[object_id]
, [idx].[index_id]
FROM
[sys].[tables] AS [tab]
INNER JOIN [sys].[indexes] AS [idx]
  ON [idx].[object_id] = [tab].[object_id]
WHERE
[idx].[is_primary_key] = 0
AND [idx].[is_unique_constraint] = 0
AND [idx].[index_id] > 0
AND [idx].[type_desc] <> 'XML'
ORDER BY
[tab].[name]
, [idx].[name];

  OPEN [IndexCur];

  WHILE 1 = 1 -- loop forever, but it won't
BEGIN
FETCH NEXT FROM [IndexCur]
INTO @TableName, @IndexName, @TypeDesc, @UniqueType, @ObjectId, @IndexId;

  IF @@FETCH_STATUS <> 0
  BREAK; -- exit if no more data to process

    SELECT
  @IndexColumns = STUFF((SELECT N', ' + QUOTENAME([c].[name]) + N' ' + CASE [ic].[is_descending_key] WHEN 0 THEN N'ASC' ELSE N'DESC' END
          FROM
           [sys].[index_columns] AS [ic]
           INNER JOIN [sys].[columns] AS [c]
           ON [c].[object_id] = [ic].[object_id] AND
            [c].[column_id] = [ic].[column_id]
          WHERE [ic].[object_id] = @ObjectId AND
            [ic].[key_ordinal] > 0 AND
            [ic].[index_id] = @IndexId
          ORDER BY [ic].[index_column_id]
          FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'')
  , @IncludeColumns = ISNULL(N'INCLUDE (' +
         STUFF((SELECT N', ' + QUOTENAME([c].[name])
           FROM
            [sys].[index_columns] AS [ic]
            INNER JOIN [sys].[columns] AS [c]
             ON [c].[object_id] = [ic].[object_id] AND
              [c].[column_id] = [ic].[column_id]
           WHERE [ic].[object_id] = @ObjectId AND
             [ic].[key_ordinal] = 0 AND
             [ic].[index_id] = @IndexId
           ORDER BY [ic].[index_column_id]
           FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'') + N')',N'');

  SELECT @SQLCmd = @SQLCmd + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@IndexCreateSQLTemplate,N'<IndexName>',@IndexName),N'<ObjectId>',@ObjectId),N'<UNIQUE>',@UniqueType),N'<IndexType>',@TypeDesc),N'<TableName>',@TableName),N'<indexColumns>',@IndexColumns),N'<IncludeClause>',@IncludeColumns);

  END

  CLOSE [IndexCur];

  DEALLOCATE [IndexCur];

  SELECT CAST(N'<![CDATA[' + @SQLCmd + N']]>' AS XML);
GO


• Sababa

  SSC Enthusiast

  Points: 150

  More actions

  September 11, 2018 at 2:52 pm

  #2005339

  Thanks, I'll try it and update you

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  September 11, 2018 at 4:28 pm

  #2005349

  My preference has been to store the original sys.indexes and sys.index_columns rows rather than storing what you generated from them.  You can always generate it later if you need to, and you'll have all the data for the index, rather than just what ended up in the script you generated.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• jcelko212 32090

  SSCrazy Eights

  Points: 9294

  More actions

  September 11, 2018 at 7:50 pm

  #2005355

  Sababa - Tuesday, September 11, 2018 12:36 PM

  We don't have there any clustered indexes and there are no primary or unique. Only nonlustered

  I'm willing to grant that it's possible not to have clustered indexes in a properly designed schema. But by definition keys have to be unique. Are you telling us that this is not really a relational database, since it has no keys, and that you're using SQL to mimic a 1970s file system?

  Please post DDL and follow ANSI/ISO standards when asking for help. 

• Grant Fritchey

  SSC Guru

  Points: 398696

  More actions

  September 12, 2018 at 4:48 am

  #2005380

  Sababa - Tuesday, September 11, 2018 12:36 PM

  We don't have there any clustered indexes and there are no primary or unique. Only nonlustered

  Oh no. No. No, no, no, no. No.

  That is a VERY bad design. No wonder you have performance problems. SQL Server lives, eats and breathes off of two things, a unique constraint and a clustered index. They don't need to be the same thing, but not having them leads to nothing but trouble in performance as well as, and more importantly, data integrity.

  This is a very bad thing and should be your primary focus if you hope to achieve a well functioning system that properly protects your businesses data. Accept no excuses. It doesn't matter who is responsible for this. It needs to be fixed.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Sababa

  SSC Enthusiast

  Points: 150

  More actions

  September 12, 2018 at 7:37 am

  #2005411

  jcelko212 32090 - Tuesday, September 11, 2018 7:50 PM

  Sababa - Tuesday, September 11, 2018 12:36 PM

  We don't have there any clustered indexes and there are no primary or unique. Only nonlustered

  I'm willing to grant that it's possible not to have clustered indexes in a properly designed schema. But by definition keys have to be unique. Are you telling us that this is not really a relational database, since it has no keys, and that you're using SQL to mimic a 1970s file system?

  Sorry for the misunderstanding, this is a relational DB and of cause, every table has keys. 
  I've posted my DDL above in one of my answers.

• Sababa

  SSC Enthusiast

  Points: 150

  More actions

  September 12, 2018 at 7:47 am

  #2005415

  Grant Fritchey - Wednesday, September 12, 2018 4:48 AM

  Sababa - Tuesday, September 11, 2018 12:36 PM

  We don't have there any clustered indexes and there are no primary or unique. Only nonlustered

  Oh no. No. No, no, no, no. No.

  That is a VERY bad design. No wonder you have performance problems. SQL Server lives, eats and breathes off of two things, a unique constraint and a clustered index. They don't need to be the same thing, but not having them leads to nothing but trouble in performance as well as, and more importantly, data integrity.

  This is a very bad thing and should be your primary focus if you hope to achieve a well functioning system that properly protects your businesses data. Accept no excuses. It doesn't matter who is responsible for this. It needs to be fixed.

  I'm not a DBA, unfortunately, and don't have a whole picture of the processes. My mission is to fix the exists problem by providing some script which will cover this gap and will be able to protect us from the indexes disappearing. 
  Our DB is relational and has unique keys.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 12, 2018 at 7:55 am

  #2005424

  ScottPletcher - Tuesday, September 11, 2018 4:28 PM

  My preference has been to store the original sys.indexes and sys.index_columns rows rather than storing what you generated from them.  You can always generate it later if you need to, and you'll have all the data for the index, rather than just what ended up in the script you generated.

  Actually, the preference would be to have the processes that cause the indexes to disappear to be fixed.  This solution is simply a band-aid.  Please, Scott, post your solution.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 12, 2018 at 7:56 am

  #2005425

  Sababa - Wednesday, September 12, 2018 7:47 AM

  Grant Fritchey - Wednesday, September 12, 2018 4:48 AM

  Sababa - Tuesday, September 11, 2018 12:36 PM

  We don't have there any clustered indexes and there are no primary or unique. Only nonlustered

  Oh no. No. No, no, no, no. No.

  That is a VERY bad design. No wonder you have performance problems. SQL Server lives, eats and breathes off of two things, a unique constraint and a clustered index. They don't need to be the same thing, but not having them leads to nothing but trouble in performance as well as, and more importantly, data integrity.

  This is a very bad thing and should be your primary focus if you hope to achieve a well functioning system that properly protects your businesses data. Accept no excuses. It doesn't matter who is responsible for this. It needs to be fixed.

  I'm not a DBA, unfortunately, and don't have a whole picture of the processes. My mission is to fix the exists problem by providing some script which will cover this gap and will be able to protect us from the indexes disappearing. 
  Our DB is relational and has unique keys.

  The problem is that you aren't fixing the problem, you are applying a band-aid.  The fix is to have the process(es) that are causing the problem fixed so that the indexes don't keep disappearing.

• Jeff Moden

  SSC Guru

  Points: 1003871

  More actions

  September 12, 2018 at 8:06 am

  #2005428

  Sababa - Tuesday, September 11, 2018 9:26 AM

  You're completely right, but it's not under our control.
  Unfortunately, we have a common DB with our ERP contractor and they may impact on that. when running any processes there.

  If they are a contractor that was hired by your company and they're the ones that are dropping the indexes, they your company needs to talk with them and get things straight or fire them.  You rebuilding indexes that they've deleted is a sign of MUCH bigger problems to come.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Sababa

  SSC Enthusiast

  Points: 150

  More actions

  September 12, 2018 at 11:05 am

  #2005466

  Lynn Pettis - Tuesday, September 11, 2018 2:34 PM

  Here is dynamic SQL that will generate the script you need.  It will be initially displayed in a column in grid mode, left clink on the link and it will take it to an xml display.  You can then copy paste the output to a query window and put it in a stored procedure that you store in the target database.  This script must also be run in the target database when all the indexes exist.  You will need to test it in a test environment to ensure it does what you expect.

  
DECLARE @IndexCreateSQLTemplate NVARCHAR(MAX)
   ,@IndexCreateSQL NVARCHAR(MAX)
   ,@SQLCmd NVARCHAR(MAX);

  SET @IndexCreateSQLTemplate = N'

  SET @IndexCreateSQL = N''
IF NOT EXISTS(SELECT 1
     FROM [sys].[indexes] AS [idx]
     WHERE [idx].[name] = ''''<IndexName>'''' AND [idx].[object_id] = OBJECT_ID(''''<TableName>'''',''''U''''))
CREATE <UNIQUE> <IndexType> INDEX <IndexName>
ON <TableName> (<indexColumns> )
<IncludeClause>
ON [DEFAULT];
'';

  EXEC [sys].[sp_executesql] @stmt = @IndexCreateSQL;

  ';

  SET @SQLCmd = N'DECLARE @IndexCreateSQL NVARCHAR(MAX);'

  DECLARE @TableName  NVARCHAR(256)
   ,@IndexName  NVARCHAR(128)
   ,@TypeDesc   NVARCHAR(128)
   ,@UniqueType  NVARCHAR(128)
   ,@ObjectId   INT
   ,@IndexId   INT
   ,@IndexColumns NVARCHAR(MAX)
   ,@IncludeColumns NVARCHAR(MAX);

  DECLARE IndexCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([tab].[object_id])) + '.' + QUOTENAME([tab].[name]) AS TableName
, QUOTENAME([idx].[name]) AS IndexName
, [idx].[type_desc]
, CASE [idx].[is_unique] WHEN 1 THEN N'UNIQUE' ELSE N'' END AS UniqueType
, [tab].[object_id]
, [idx].[index_id]
FROM
[sys].[tables] AS [tab]
INNER JOIN [sys].[indexes] AS [idx]
  ON [idx].[object_id] = [tab].[object_id]
WHERE
[idx].[is_primary_key] = 0
AND [idx].[is_unique_constraint] = 0
AND [idx].[index_id] > 0
AND [idx].[type_desc] <> 'XML'
ORDER BY
[tab].[name]
, [idx].[name];

  OPEN [IndexCur];

  WHILE 1 = 1 -- loop forever, but it won't
BEGIN
FETCH NEXT FROM [IndexCur]
INTO @TableName, @IndexName, @TypeDesc, @UniqueType, @ObjectId, @IndexId;

  IF @@FETCH_STATUS <> 0
  BREAK; -- exit if no more data to process

    SELECT
  @IndexColumns = STUFF((SELECT N', ' + QUOTENAME([c].[name]) + N' ' + CASE [ic].[is_descending_key] WHEN 0 THEN N'ASC' ELSE N'DESC' END
          FROM
           [sys].[index_columns] AS [ic]
           INNER JOIN [sys].[columns] AS [c]
           ON [c].[object_id] = [ic].[object_id] AND
            [c].[column_id] = [ic].[column_id]
          WHERE [ic].[object_id] = @ObjectId AND
            [ic].[key_ordinal] > 0 AND
            [ic].[index_id] = @IndexId
          ORDER BY [ic].[index_column_id]
          FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'')
  , @IncludeColumns = ISNULL(N'INCLUDE (' +
         STUFF((SELECT N', ' + QUOTENAME([c].[name])
           FROM
            [sys].[index_columns] AS [ic]
            INNER JOIN [sys].[columns] AS [c]
             ON [c].[object_id] = [ic].[object_id] AND
              [c].[column_id] = [ic].[column_id]
           WHERE [ic].[object_id] = @ObjectId AND
             [ic].[key_ordinal] = 0 AND
             [ic].[index_id] = @IndexId
           ORDER BY [ic].[index_column_id]
           FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'') + N')',N'');

  SELECT @SQLCmd = @SQLCmd + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@IndexCreateSQLTemplate,N'<IndexName>',@IndexName),N'<ObjectId>',@ObjectId),N'<UNIQUE>',@UniqueType),N'<IndexType>',@TypeDesc),N'<TableName>',@TableName),N'<indexColumns>',@IndexColumns),N'<IncludeClause>',@IncludeColumns);

  END

  CLOSE [IndexCur];

  DEALLOCATE [IndexCur];

  SELECT CAST(N'<![CDATA[' + @SQLCmd + N']]>' AS XML);
GO


  It works!!!!!!!
  Thank you so much!!!
  Have a great day!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 12, 2018 at 11:20 am

  #2005469

  Sababa - Wednesday, September 12, 2018 11:05 AM

  Lynn Pettis - Tuesday, September 11, 2018 2:34 PM

  Here is dynamic SQL that will generate the script you need.  It will be initially displayed in a column in grid mode, left clink on the link and it will take it to an xml display.  You can then copy paste the output to a query window and put it in a stored procedure that you store in the target database.  This script must also be run in the target database when all the indexes exist.  You will need to test it in a test environment to ensure it does what you expect.

  
DECLARE @IndexCreateSQLTemplate NVARCHAR(MAX)
   ,@IndexCreateSQL NVARCHAR(MAX)
   ,@SQLCmd NVARCHAR(MAX);

  SET @IndexCreateSQLTemplate = N'

  SET @IndexCreateSQL = N''
IF NOT EXISTS(SELECT 1
     FROM [sys].[indexes] AS [idx]
     WHERE [idx].[name] = ''''<IndexName>'''' AND [idx].[object_id] = OBJECT_ID(''''<TableName>'''',''''U''''))
CREATE <UNIQUE> <IndexType> INDEX <IndexName>
ON <TableName> (<indexColumns> )
<IncludeClause>
ON [DEFAULT];
'';

  EXEC [sys].[sp_executesql] @stmt = @IndexCreateSQL;

  ';

  SET @SQLCmd = N'DECLARE @IndexCreateSQL NVARCHAR(MAX);'

  DECLARE @TableName  NVARCHAR(256)
   ,@IndexName  NVARCHAR(128)
   ,@TypeDesc   NVARCHAR(128)
   ,@UniqueType  NVARCHAR(128)
   ,@ObjectId   INT
   ,@IndexId   INT
   ,@IndexColumns NVARCHAR(MAX)
   ,@IncludeColumns NVARCHAR(MAX);

  DECLARE IndexCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([tab].[object_id])) + '.' + QUOTENAME([tab].[name]) AS TableName
, QUOTENAME([idx].[name]) AS IndexName
, [idx].[type_desc]
, CASE [idx].[is_unique] WHEN 1 THEN N'UNIQUE' ELSE N'' END AS UniqueType
, [tab].[object_id]
, [idx].[index_id]
FROM
[sys].[tables] AS [tab]
INNER JOIN [sys].[indexes] AS [idx]
  ON [idx].[object_id] = [tab].[object_id]
WHERE
[idx].[is_primary_key] = 0
AND [idx].[is_unique_constraint] = 0
AND [idx].[index_id] > 0
AND [idx].[type_desc] <> 'XML'
ORDER BY
[tab].[name]
, [idx].[name];

  OPEN [IndexCur];

  WHILE 1 = 1 -- loop forever, but it won't
BEGIN
FETCH NEXT FROM [IndexCur]
INTO @TableName, @IndexName, @TypeDesc, @UniqueType, @ObjectId, @IndexId;

  IF @@FETCH_STATUS <> 0
  BREAK; -- exit if no more data to process

    SELECT
  @IndexColumns = STUFF((SELECT N', ' + QUOTENAME([c].[name]) + N' ' + CASE [ic].[is_descending_key] WHEN 0 THEN N'ASC' ELSE N'DESC' END
          FROM
           [sys].[index_columns] AS [ic]
           INNER JOIN [sys].[columns] AS [c]
           ON [c].[object_id] = [ic].[object_id] AND
            [c].[column_id] = [ic].[column_id]
          WHERE [ic].[object_id] = @ObjectId AND
            [ic].[key_ordinal] > 0 AND
            [ic].[index_id] = @IndexId
          ORDER BY [ic].[index_column_id]
          FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'')
  , @IncludeColumns = ISNULL(N'INCLUDE (' +
         STUFF((SELECT N', ' + QUOTENAME([c].[name])
           FROM
            [sys].[index_columns] AS [ic]
            INNER JOIN [sys].[columns] AS [c]
             ON [c].[object_id] = [ic].[object_id] AND
              [c].[column_id] = [ic].[column_id]
           WHERE [ic].[object_id] = @ObjectId AND
             [ic].[key_ordinal] = 0 AND
             [ic].[index_id] = @IndexId
           ORDER BY [ic].[index_column_id]
           FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'') + N')',N'');

  SELECT @SQLCmd = @SQLCmd + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@IndexCreateSQLTemplate,N'<IndexName>',@IndexName),N'<ObjectId>',@ObjectId),N'<UNIQUE>',@UniqueType),N'<IndexType>',@TypeDesc),N'<TableName>',@TableName),N'<indexColumns>',@IndexColumns),N'<IncludeClause>',@IncludeColumns);

  END

  CLOSE [IndexCur];

  DEALLOCATE [IndexCur];

  SELECT CAST(N'<![CDATA[' + @SQLCmd + N']]>' AS XML);
GO


  It works!!!!!!!
  Thank you so much!!!
  Have a great day!

  You are welcome for the band-aid.  Hope you get the real problem fixed so you won't need it too long.

• Sababa

  SSC Enthusiast

  Points: 150

  More actions

  September 12, 2018 at 1:29 pm

  #2005495

  Lynn Pettis - Wednesday, September 12, 2018 11:20 AM

  Sababa - Wednesday, September 12, 2018 11:05 AM

  Lynn Pettis - Tuesday, September 11, 2018 2:34 PM

  Here is dynamic SQL that will generate the script you need.  It will be initially displayed in a column in grid mode, left clink on the link and it will take it to an xml display.  You can then copy paste the output to a query window and put it in a stored procedure that you store in the target database.  This script must also be run in the target database when all the indexes exist.  You will need to test it in a test environment to ensure it does what you expect.

  
DECLARE @IndexCreateSQLTemplate NVARCHAR(MAX)
   ,@IndexCreateSQL NVARCHAR(MAX)
   ,@SQLCmd NVARCHAR(MAX);

  SET @IndexCreateSQLTemplate = N'

  SET @IndexCreateSQL = N''
IF NOT EXISTS(SELECT 1
     FROM [sys].[indexes] AS [idx]
     WHERE [idx].[name] = ''''<IndexName>'''' AND [idx].[object_id] = OBJECT_ID(''''<TableName>'''',''''U''''))
CREATE <UNIQUE> <IndexType> INDEX <IndexName>
ON <TableName> (<indexColumns> )
<IncludeClause>
ON [DEFAULT];
'';

  EXEC [sys].[sp_executesql] @stmt = @IndexCreateSQL;

  ';

  SET @SQLCmd = N'DECLARE @IndexCreateSQL NVARCHAR(MAX);'

  DECLARE @TableName  NVARCHAR(256)
   ,@IndexName  NVARCHAR(128)
   ,@TypeDesc   NVARCHAR(128)
   ,@UniqueType  NVARCHAR(128)
   ,@ObjectId   INT
   ,@IndexId   INT
   ,@IndexColumns NVARCHAR(MAX)
   ,@IncludeColumns NVARCHAR(MAX);

  DECLARE IndexCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([tab].[object_id])) + '.' + QUOTENAME([tab].[name]) AS TableName
, QUOTENAME([idx].[name]) AS IndexName
, [idx].[type_desc]
, CASE [idx].[is_unique] WHEN 1 THEN N'UNIQUE' ELSE N'' END AS UniqueType
, [tab].[object_id]
, [idx].[index_id]
FROM
[sys].[tables] AS [tab]
INNER JOIN [sys].[indexes] AS [idx]
  ON [idx].[object_id] = [tab].[object_id]
WHERE
[idx].[is_primary_key] = 0
AND [idx].[is_unique_constraint] = 0
AND [idx].[index_id] > 0
AND [idx].[type_desc] <> 'XML'
ORDER BY
[tab].[name]
, [idx].[name];

  OPEN [IndexCur];

  WHILE 1 = 1 -- loop forever, but it won't
BEGIN
FETCH NEXT FROM [IndexCur]
INTO @TableName, @IndexName, @TypeDesc, @UniqueType, @ObjectId, @IndexId;

  IF @@FETCH_STATUS <> 0
  BREAK; -- exit if no more data to process

    SELECT
  @IndexColumns = STUFF((SELECT N', ' + QUOTENAME([c].[name]) + N' ' + CASE [ic].[is_descending_key] WHEN 0 THEN N'ASC' ELSE N'DESC' END
          FROM
           [sys].[index_columns] AS [ic]
           INNER JOIN [sys].[columns] AS [c]
           ON [c].[object_id] = [ic].[object_id] AND
            [c].[column_id] = [ic].[column_id]
          WHERE [ic].[object_id] = @ObjectId AND
            [ic].[key_ordinal] > 0 AND
            [ic].[index_id] = @IndexId
          ORDER BY [ic].[index_column_id]
          FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'')
  , @IncludeColumns = ISNULL(N'INCLUDE (' +
         STUFF((SELECT N', ' + QUOTENAME([c].[name])
           FROM
            [sys].[index_columns] AS [ic]
            INNER JOIN [sys].[columns] AS [c]
             ON [c].[object_id] = [ic].[object_id] AND
              [c].[column_id] = [ic].[column_id]
           WHERE [ic].[object_id] = @ObjectId AND
             [ic].[key_ordinal] = 0 AND
             [ic].[index_id] = @IndexId
           ORDER BY [ic].[index_column_id]
           FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'') + N')',N'');

  SELECT @SQLCmd = @SQLCmd + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@IndexCreateSQLTemplate,N'<IndexName>',@IndexName),N'<ObjectId>',@ObjectId),N'<UNIQUE>',@UniqueType),N'<IndexType>',@TypeDesc),N'<TableName>',@TableName),N'<indexColumns>',@IndexColumns),N'<IncludeClause>',@IncludeColumns);

  END

  CLOSE [IndexCur];

  DEALLOCATE [IndexCur];

  SELECT CAST(N'<![CDATA[' + @SQLCmd + N']]>' AS XML);
GO


  It works!!!!!!!
  Thank you so much!!!
  Have a great day!

  You are welcome for the band-aid.  Hope you get the real problem fixed so you won't need it too long.

  BTW, is there an option to receive any alert when the index disappears? Using the same script, of cause, which will recreate this index

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 12, 2018 at 3:17 pm

  #2005519

  Sababa - Wednesday, September 12, 2018 1:29 PM

  Lynn Pettis - Wednesday, September 12, 2018 11:20 AM

  Sababa - Wednesday, September 12, 2018 11:05 AM

  Lynn Pettis - Tuesday, September 11, 2018 2:34 PM

  Here is dynamic SQL that will generate the script you need.  It will be initially displayed in a column in grid mode, left clink on the link and it will take it to an xml display.  You can then copy paste the output to a query window and put it in a stored procedure that you store in the target database.  This script must also be run in the target database when all the indexes exist.  You will need to test it in a test environment to ensure it does what you expect.

  
DECLARE @IndexCreateSQLTemplate NVARCHAR(MAX)
   ,@IndexCreateSQL NVARCHAR(MAX)
   ,@SQLCmd NVARCHAR(MAX);

  SET @IndexCreateSQLTemplate = N'

  SET @IndexCreateSQL = N''
IF NOT EXISTS(SELECT 1
     FROM [sys].[indexes] AS [idx]
     WHERE [idx].[name] = ''''<IndexName>'''' AND [idx].[object_id] = OBJECT_ID(''''<TableName>'''',''''U''''))
CREATE <UNIQUE> <IndexType> INDEX <IndexName>
ON <TableName> (<indexColumns> )
<IncludeClause>
ON [DEFAULT];
'';

  EXEC [sys].[sp_executesql] @stmt = @IndexCreateSQL;

  ';

  SET @SQLCmd = N'DECLARE @IndexCreateSQL NVARCHAR(MAX);'

  DECLARE @TableName  NVARCHAR(256)
   ,@IndexName  NVARCHAR(128)
   ,@TypeDesc   NVARCHAR(128)
   ,@UniqueType  NVARCHAR(128)
   ,@ObjectId   INT
   ,@IndexId   INT
   ,@IndexColumns NVARCHAR(MAX)
   ,@IncludeColumns NVARCHAR(MAX);

  DECLARE IndexCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME([tab].[object_id])) + '.' + QUOTENAME([tab].[name]) AS TableName
, QUOTENAME([idx].[name]) AS IndexName
, [idx].[type_desc]
, CASE [idx].[is_unique] WHEN 1 THEN N'UNIQUE' ELSE N'' END AS UniqueType
, [tab].[object_id]
, [idx].[index_id]
FROM
[sys].[tables] AS [tab]
INNER JOIN [sys].[indexes] AS [idx]
  ON [idx].[object_id] = [tab].[object_id]
WHERE
[idx].[is_primary_key] = 0
AND [idx].[is_unique_constraint] = 0
AND [idx].[index_id] > 0
AND [idx].[type_desc] <> 'XML'
ORDER BY
[tab].[name]
, [idx].[name];

  OPEN [IndexCur];

  WHILE 1 = 1 -- loop forever, but it won't
BEGIN
FETCH NEXT FROM [IndexCur]
INTO @TableName, @IndexName, @TypeDesc, @UniqueType, @ObjectId, @IndexId;

  IF @@FETCH_STATUS <> 0
  BREAK; -- exit if no more data to process

    SELECT
  @IndexColumns = STUFF((SELECT N', ' + QUOTENAME([c].[name]) + N' ' + CASE [ic].[is_descending_key] WHEN 0 THEN N'ASC' ELSE N'DESC' END
          FROM
           [sys].[index_columns] AS [ic]
           INNER JOIN [sys].[columns] AS [c]
           ON [c].[object_id] = [ic].[object_id] AND
            [c].[column_id] = [ic].[column_id]
          WHERE [ic].[object_id] = @ObjectId AND
            [ic].[key_ordinal] > 0 AND
            [ic].[index_id] = @IndexId
          ORDER BY [ic].[index_column_id]
          FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'')
  , @IncludeColumns = ISNULL(N'INCLUDE (' +
         STUFF((SELECT N', ' + QUOTENAME([c].[name])
           FROM
            [sys].[index_columns] AS [ic]
            INNER JOIN [sys].[columns] AS [c]
             ON [c].[object_id] = [ic].[object_id] AND
              [c].[column_id] = [ic].[column_id]
           WHERE [ic].[object_id] = @ObjectId AND
             [ic].[key_ordinal] = 0 AND
             [ic].[index_id] = @IndexId
           ORDER BY [ic].[index_column_id]
           FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,N'') + N')',N'');

  SELECT @SQLCmd = @SQLCmd + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@IndexCreateSQLTemplate,N'<IndexName>',@IndexName),N'<ObjectId>',@ObjectId),N'<UNIQUE>',@UniqueType),N'<IndexType>',@TypeDesc),N'<TableName>',@TableName),N'<indexColumns>',@IndexColumns),N'<IncludeClause>',@IncludeColumns);

  END

  CLOSE [IndexCur];

  DEALLOCATE [IndexCur];

  SELECT CAST(N'<![CDATA[' + @SQLCmd + N']]>' AS XML);
GO


  It works!!!!!!!
  Thank you so much!!!
  Have a great day!

  You are welcome for the band-aid.  Hope you get the real problem fixed so you won't need it too long.

  BTW, is there an option to receive any alert when the index disappears? Using the same script, of cause, which will recreate this index

  Code is yours now.  If there are changes you need, you should figure that out.  It is a band-aid.  It should NOT be a permanent process.