--IN SOME
SCENARIOS WHERE WE NEED TO DROP INDEX BEFORE LOADING DATA AND -- CREATING
AFTER. BELOW SCRIPTS WILL HELP YOU.. WE MAY NEED NECESSARY CHANGES WHERE NEEDED
-- FIRST CREATE
TABLE TO STORE SCRIPTS TO DELETE AND CREATE INDEXS .
CREATE TABLE [dbo].[TempSqlScriptsS](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BtlId] [int] NOT NULL,
[TableName] [varchar](500) NOT NULL,
[Create] [bit] NOT NULL,
[sql] [varchar](max) NOT NULL,
CONSTRAINT
[PK_TempSqlScriptsS] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--CREATE SPROC
NOW WHCIH TAKE INPUT AS SCHEMA , TABLE AND Indexid(IF NEEDED) AND GENETEA ALL INDEXES AVAILABLE ON PARTICULAR
TABLE.
CREATE PROCEDURE [dbo].[uspScriptIndex]
@STName varchar(50),
@SSName varchar(50),
@Btlid int
AS
BEGIN
SET NOCOUNT
ON
-- Get all existing indexes, but NOT
the primary keys
DECLARE cIX CURSOR FOR
SELECT DISTINCT
SCHEMA_NAME(tbl.schema_id)
[TableSchema]
,tbl.name AS [TableName]
,i.Object_ID AS [ObjectId]
,i.name AS [IndexName]
,i.Index_ID
,filegroup_name(i.data_space_id)
FileGroup
FROM sys.tables AS tbl
INNER JOIN sys.indexes i
ON (i.index_id > 0 and i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
WHERE i.is_unique = 0
AND i.is_primary_key = 0
AND tbl.name = @STName
AND SCHEMA_NAME(tbl.schema_id) = @SSName
DECLARE @IxSchema SYSNAME
,@IxTable SYSNAME
,@IxTableID INT
,@IxName SYSNAME
,@IxID INT
,@FileGroup varchar(50)
-- Loop through all indexes
OPEN cIX
FETCH NEXT
FROM cIX INTO
@IxSchema, @IxTable,
@IxTableID, @IxName,
@IxID, @FileGroup
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 '
ELSE
SET @IXSQL = @IXSQL + 'NONCLUSTERED '
SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @IxSchema + '].[' + @IxTable + '] ('
-- 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.Index_Column_ID
DECLARE @IxColumn SYSNAME
,@IxFirstColumn BIT = 1
,@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.Index_Column_ID
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)
SELECT
@IxFirstColumn = 0
,@IXSQL
= @IXSQL + 'INCLUDE ('
ELSE
SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
FETCH NEXT FROM
cIxIncColumn INTO @IxColumn, @IxIsDescending
IF (@@FETCH_STATUS != 0) SET @IXSQL = @IXSQL + ')'
END
SET @IXSQL = @IXSQL + ' WITH (MAXDOP=6) ON [' +
@FileGroup + ']'
CLOSE cIxIncColumn
DEALLOCATE cIxIncColumn
INSERT INTO EQMDC_Staging.dbo.TempSqlScriptsS
(Btlid
,TableName
,[Create]
,[sql])
SELECT
@Btlid Btlid
,@STName TableName
,1 [Create]
,'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N''[' + @IxSchema + '].[' + @IxTable + ']'')
AND name = N''' + @IxName + ''')
' + @IXSQL [sql]
UNION ALL
SELECT
@Btlid Btlid
,@STName TableName
,0 [Create]
,'IF EXISTS(SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N''[' + @IxSchema + '].[' + @IxTable + ']'')
AND name = N''' + @IxName + ''')
DROP INDEX [' + @IxName + '] ON [' + @IxSchema + '].[' + @IxTable + '] ' +
CASE WHEN INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1
THEN 'WITH (MAXDOP=6)'
ELSE ''
END
[sql]
FETCH NEXT FROM cIX INTO @IxSchema,
@IxTable, @IxTableID,
@IxName, @IxID,
@FileGroup
END
CLOSE cIX
DEALLOCATE cIX
END
GO
-- NOW HOW TO EXECUTE ABOVE S-PROC TO SCRIPT INDEXES
ON TABLE
EXEC dbo.uspScriptIndex @STName
= 'TableName', @SSName = 'dbo', @Btlid = 3109
-- NOW YOU CAN USE
YOUR TABLE ( [dbo].[TempSqlScriptsS])WHICH CONTAINS INDEX SCRIPT WHERE
NECESSARY
