Scripting all the Indexes of a Database


DECLARE IndexCursor CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM sys.indexes SI
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable varchar(50)
DECLARE @IxTableID INT
DECLARE @IxName varchar(50)
DECLARE @IxID INT

-- Loop through all indexes
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @IXSQL NVARCHAR(4000)

   SET @IXSQL = 'CREATE '

   -- Check if the index is unique
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
      SET @IXSQL = @IXSQL + 'UNIQUE '

   -- Check if the index is clustered
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
      SET @IXSQL = @IXSQL + 'CLUSTERED '

   SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON [' + @IxTable + '] ('

   -- Get all columns of the index
   DECLARE IndexCursorColumn CURSOR FOR
   SELECT SC.Name,IC.[is_included_column],IC.is_descending_key
   FROM sys.index_columns IC
   JOIN sys.columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
   WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
   ORDER BY IC.Index_Column_ID,IC.is_included_column

   DECLARE @IxColumn varchar(50)
   DECLARE @IxIncl bit
   DECLARE @Desc bit
   DECLARE @IxIsIncl bit set @IxIsIncl = 0
   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

   -- Loop throug all columns of the index and append them to the CREATE statement
   OPEN IndexCursorColumn
   FETCH NEXT FROM IndexCursorColumn INTO @IxColumn, @IxIncl, @Desc
   WHILE (@@FETCH_STATUS = 0)
   BEGIN

      IF (@IxFirstColumn = 1)
            BEGIN
         SET @IxFirstColumn = 0
            END
      ELSE
            BEGIN
                  --check to see if it's an included column
                  IF ((@IxIsIncl = 0) AND (@IxIncl = 1))
                  BEGIN
                   SET @IxIsIncl = 1
                   SET @IXSQL = @IXSQL + ') INCLUDE ('
                  END
                  ELSE
                  BEGIN
                   SET @IXSQL = @IXSQL + ', '
                  END
            END

      SET @IXSQL = @IXSQL + '[' + @IxColumn + ']'
            --check to see if it's DESC
            IF @Desc = 1
                  SET @IXSQL = @IXSQL + ' DESC'

      FETCH NEXT FROM IndexCursorColumn INTO @IxColumn, @IxIncl, @Desc
   END
   CLOSE IndexCursorColumn
   DEALLOCATE IndexCursorColumn

   SET @IXSQL = @IXSQL + ')'
   -- Print out the CREATE statement for the index
   PRINT @IXSQL

   FETCH NEXT FROM IndexCursor INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE IndexCursor
DEALLOCATE IndexCursor

Comments

Post a Comment

Popular posts from this blog

SSIS vs. T-SQL

This is a common question which comes often before any developer to choose between the two as to follow which path to choose as SSIS or T-SQL for developing ETL solutions. well i also got similar scenario some time back and was in a dilemma to decide this. on a big picture it's always better to go with conventional way i.e. using T-SQL and not to go with the tool. But on doing some more reading and research over many blogs and forums and also the development phase of my work over some 2 and a half odd years. I found some good reasons and explanations over this as not to always stick to a particular approach and do proper analysis of the requirements to take a decision. Below are some of the key points which i was able to collect regarding this. 1.  If the data does need to be persisted temporarily it can be to a raw file in SSIS whereas T-SQL requires temporary tables for which there may be a management/security overhead. 2.  SSIS have a very good visual representatio
Read more

Sync SSAS Cube From One Environment to Another

Please Execute the Below Mentioned Script in Either the SQL Agent Job or XMLA Script Window or in the SSIS Package using the "Analysis Services Execute DDL Task" -  < Synchronize  xmlns:xsi = " https://www.w3.org/2001/XMLSchema-instance " xmlns:xsd = " https://www.w3.org/2001/XMLSchema " xmlns = " https://schemas.microsoft.com/analysisservices/2003/engine " > < Source > < ConnectionString > Provider=MSOLAP.6;Data Source=SOURCESERVER; Integrated Security=SSPI;Initial Catalog=SourceCubeName </ ConnectionString > < Object > < DatabaseID > SourceCubeName </ DatabaseID > </ Object > </ Source > < SynchronizeSecurity > CopyAll </ SynchronizeSecurity > < ApplyCompression > true </ ApplyCompression > </ Synchronize >
Read more

SQL Function to replace special characters from a string

CREATE Function [dbo].[replaceSpecialCharacters] (@str varchar(256)) returns varchar(256) with schemabinding begin SET @str = LTRIM(RTRIM(@str))     DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,{,},(,),.,!,:,?,^,+,=,;,-,/,\]%'       WHILE PATINDEX( @expres, @str ) > 0          BEGIN   SET @str = REPLACE(@str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),'_') END SET @str = Replace(@str,' ','||') SET @str = Replace(@str,'’','') SET @str = Replace(@str,'_','') SET @str = Replace(@str,'||','_') SET @str = Replace(@str,'__','_') SET @str = Replace(@str,'__','_') SET @str = LTRIM(RTRIM(@str))     return @str END GO
Read more