--HOW TO COPY
INDEXS FROM ONE TABLE TO ANOTHER TEMP TABLE?
-- BLOW SPROC
WILL COPY INDEX FROM ONE TABLE TO ANOTHER TEMP TABLE?
CREATE PROCEDURE [dbo].[uspCopyIndex]
@STName varchar(50)
,@SSName varchar(50)
,@DTName varchar(50)
,@DSName varchar(50)
,@ClusteredOnly bit
AS
BEGIN
-- Get all existing indexes
DECLARE cIX CURSOR FOR
SELECT DISTINCT
SCHEMA_NAME(tbl.schema_id) AS [TableSchema],
tbl.name AS [TableName]
,i.Object_ID AS [ObjectId],
i.name AS [IndexName]
,i.Index_ID
,i.is_primary_key IsPrimaryKey
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
WHERE tbl.name = @STName AND SCHEMA_NAME(tbl.schema_id) = @SSName
DECLARE @IxSchema SYSNAME
,@IxTable SYSNAME
, @IxTableID INT
, @IxName SYSNAME
, @IxID INT
,@IsPrimaryKey BIT
-- Loop through all indexes
OPEN cIX
FETCH NEXT
FROM cIX INTO
@IxSchema, @IxTable,
@IxTableID, @IxName,
@IxID, @IsPrimaryKey
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Check if clustered
IF @ClusteredOnly = 0 OR (@ClusteredOnly = 1 AND INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
BEGIN
DECLARE @IXSQL NVARCHAR(4000)
SET @IXSQL = 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE
object_id = OBJECT_ID(N''[' + @DSName + '].[' + @DTName + ']'') AND name = N''' +
@IxName + ''')' + CHAR(10) + CHAR(13)
IF @IsPrimaryKey = 0
BEGIN
SET @IXSQL = @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 '
ELSE SET @IXSQL = @IXSQL + 'NONCLUSTERED '
SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @DSName + '].' + @DTName + ' ('
END
ELSE IF @IsPrimaryKey = 1
BEGIN
SET @IXSQL = @IXSQL + 'ALTER TABLE [' +
@DSName + '].' + @DTName + ' ADD CONSTRAINT [Aux' +
@IxName + '] PRIMARY
KEY '
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1) SET @IXSQL = @IXSQL + 'CLUSTERED ('
ELSE SET @IXSQL = @IXSQL + 'NONCLUSTERED ('
END
-- Get all columns of the
index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name, 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 AND IC.is_included_column =
0
ORDER BY IC.key_ordinal
DECLARE @IxColumn SYSNAME
DECLARE
@IxFirstColumn BIT SET
@IxFirstColumn = 1
DECLARE
@IxIsDescending BIT
-- Loop throug all
columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn,
@IxIsDescending
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1) SET @IxFirstColumn = 0
ELSE SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
IF (@IxIsDescending = 1) SET @IXSQL = @IXSQL + ' DESC' ELSE SET @IXSQL = @IXSQL + ' ASC'
FETCH NEXT FROM cIxColumn INTO
@IxColumn, @IxIsDescending
END
CLOSE cIxColumn
DEALLOCATE cIxColumn
SET @IXSQL = @IXSQL + ') '
-- Get all included
columns of the index
DECLARE
cIxIncColumn CURSOR FOR
SELECT SC.Name, 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 AND IC.is_included_column =
1
ORDER BY IC.key_ordinal
SET @IxFirstColumn = 1
-- Loop throug all
columns of the index and append them to the CREATE statement
OPEN cIxIncColumn
FETCH NEXT FROM
cIxIncColumn INTO @IxColumn, @IxIsDescending
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
BEGIN
SET
@IxFirstColumn = 0
SET
@IXSQL = @IXSQL +
'INCLUDE ('
END
ELSE SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
FETCH NEXT FROM
cIxIncColumn INTO @IxColumn, @IxIsDescending
IF (@@FETCH_STATUS != 0) SET @IXSQL = @IXSQL + ') '
END
CLOSE cIxIncColumn
DEALLOCATE
cIxIncColumn
SET @IXSQL = @IXSQL + ' WITH (MAXDOP=6)'
EXEC (@IXSQL)
END
FETCH NEXT FROM cIX INTO @IxSchema,
@IxTable, @IxTableID,
@IxName, @IxID,
@IsPrimaryKey
END
CLOSE cIX
DEALLOCATE cIX
END
E.G
--MAKE SURE BOTH
TABLE SHOULD HAVE SAME COLUMNS NAME
EXEC EXEC uspCopyIndex
@STName = 'FactActivity'
,@SSName
= 'dbo'
,@DTName
= 'FactActivityAux'
,@DSName
= 'dbo'
,@ClusteredOnly = 0
