How to copy indexes from one table to another temp table

--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

How to script Index Names into temp table to load data faster

--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

User Defined Table Type

--How to use USER DEFINED TABLE TYPE

--Create table type

CREATE TYPE [dbo].[udtId] AS TABLE(

       [Id] [int] NOT NULL,

       PRIMARY KEY CLUSTERED

(

       [Id] ASC

)

--Create table Customer and insert data

CREATE TABLE [dbo].[customer](

       [ID] [int] NOT NULL,

       [Name] [varchar](50) NULL,

       [Add] [nvarchar](50) NULL,

       [b] [bit] NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[customer]

           ([ID]

           ,[Name]

           ,[Add]

           ,[b])

     VALUES

           (1,'HYD','UK',1),

           (2,'Wembley','London',1),

           (3,'HYT','USA',0),

           (43,'Cardiff','Wales',0)

GO

--Create Sproc using table datatype

CREATE PROC tblCustomer

@PlanIds udtId

AS

BEGIN

       Update dbo.customer SET Name = 'Pram' WHERE Id in (SELECT id FROM @PlanIds)

END

--Now Execute Store Proc

DECLARE @PlanIds udtId

INSERT INTO @PlanIds SELECT Id FROM customer

       EXEC tblCustomer @PlanIds = @PlanIds

How to convert rows in to column

--How to get ids in one line usign , Delimeted 

DECLARE @planids TABLE (Id INT)

INSERT INTO @planids VALUES (123),(234),(343),(543),(45345),(543),(4534)

SELECT * FROM @planids

DECLARE @plans varchar(max)

SET @plans = ''

SELECT  @plans = @plans+ cast(Id AS varchar(12)) +',  '

FROM @planids 

--where ParentPlanId in (373289)

SELECT @plans AS PlanList