Posts

Showing posts from April, 2012

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 + &#