Tuesday 4 December 2012

Copying Table with INDEX and CONSTRAINTS

Introduction
When we are trying to copy a table (Structure only or with Data) we are unable to copy the Indexes, constraint with it.
Can we copy Indexes and Constraint with Table structure in the same database?
This article is related to it.
General Method of Copying Table Structure
To understand it properly I am trying to make an example.
Step-1 [ Base Table Its Indexes and Constraints ]


IF OBJECT_ID('my_EMPTABLE') IS NOT NULL
   BEGIN
     DROP TABLE my_EMPTABLE;
   END
GO
-- Creating Base Table  
CREATE TABLE my_EMPTABLE
(
      EMPID     int IDENTITY(1,1) NOT NULL,
      EMPNAME   varchar(50) NOT NULL,
      EMPGRADE  varchar(1)  NOT NULL,
      EMPDEPT   varchar(50) NOT NULL,
      EMPCITY   varchar(50) NOT NULL,
      EMPSTATE  varchar(50) NOT NULL,
      CONSTRAINT PK__my_EMPTABLE_EMPID PRIMARY KEY CLUSTERED
                  (
                        EMPID ASC
                  )
)
GO
-- Creating NonClustered Index
CREATE NONCLUSTERED INDEX IX_NonCLUS_my_EMPTABLE_EMPCITY
ON my_EMPTABLE(EMPCITY);
GO

-- Creating Default constraint
ALTER TABLE my_EMPTABLE ADD  CONSTRAINT Const_DFLT_my_EMPTABLE_EMPSTATE 
DEFAULT ('TRIPURA') FOR [EMPSTATE];
GO

Step-2 [ Insert Some Records ]
 
INSERT INTO my_EMPTABLE 
      (EMPNAME, EMPGRADE, EMPDEPT, EMPCITY, EMPSTATE)
VALUES('JOYDEEP DAS', 'B', 'DEV', 'AGARTALA', 'TRIPURA'),
      ('TUHIN SHINAH', 'B', 'DEV', 'KOLKATA', 'WEST BENGAL');
Step-3 [ The Simple way to Copy Table ]
To copy table with records
SELECT EMPID, EMPNAME, EMPGRADE, EMPDEPT, EMPCITY, EMPSTATE
INTO   my_EMPTABLE_CPY  
FROM   my_EMPTABLE;
To copy table structure only
SELECT EMPID, EMPNAME, EMPGRADE, EMPDEPT, EMPCITY, EMPSTATE
INTO   my_EMPTABLE_CPY  
FROM   my_EMPTABLE
WHERE  1 = 2;
Step-4 [ So how we copy Table with All of it Index and Constraint ]
In the above example the Index and constraint are not copied.
You can not directly copy the constraint from one table to another table; first you should copy the table Structure with indexes and constraint, to do so
References: (Hardi Patel)
Please follow the instructions below:
 1. Select the DATABASE from which you want to copy the table, then right Click on that and Select the TASK then Select Generate Script.
Database -> Task -> Generate Scripts...
You will get the Wizard
follow it.
Step 1. Press Next button
Step 2. Select Database -> Select the database from list and Press Next button
Step 3. Choose Script Options -> Table/View Options -> Select two indexes option and change both to True.
Script Full-Text Indexes -> True and Script Indexes -> True. Press NEXT
Step 4. Choose Object Type -> Tables
Step 5. Choose Tables -> select tables you want to generate script. Press NEXT.
Step 6. Output Option -> select Script to New Query Window options. Press Finish.
Step 7. Press Finish

Step-5 [ Alternate Way to copy Table with All of it Index and Constraint ]
We can use this stored procedure to perform this work. This Stored Procedure is ready to use.
/*

Testing
--------
EXEC my_TBLCOPY
     @p_STRUCONLY      = 1,
     @p_SOURCETBL      = 'sale_tfa_it',
     @p_DESTINATIONTBL = 'my_it'
    


IMPORTANT: Will not work with full-text, spatial, or XML indexes.
*/    
IF OBJECT_ID('my_TBLCOPY') IS NOT NULL
   BEGIN
      DROP PROCEDURE my_TBLCOPY;
   END
GO       
CREATE PROCEDURE my_TBLCOPY
       (
         @p_STRUCONLY      INT          = 1,
         @p_SOURCETBL      VARCHAR(255) = NULL,
         @p_DESTINATIONTBL VARCHAR(255) = NULL
       )
AS
              DECLARE @my_Table   nVARCHAR(255) = '',
                      @intRow_Cnt INT = 1,
                      @execStr    nVARCHAR(MAX),
                      @curPk      nVARCHAR(255),
                      @MaxRows    INT,
                      @execPK     nVARCHAR(255) = NULL;
              CREATE TABLE #tmp_Indxs
                           (
                             rnum INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
                             execStr NVARCHAR(MAX)
                           );
BEGIN
     SET NOCOUNT ON;
     BEGIN TRY
     SET @my_Table = @p_SOURCETBL;
     WITH qry (NAME, TYPE_DESC, IS_UNIQUE, IGNORE_DUP_KEY,
               FILL_FACTOR, PAD_INDEX, ROW_LOCKS, PAGE_LOCKS,
               COLUMN_NNAME, KEY_ORD, KEY_DESC)
           AS (SELECT ind.NAME, ind.type_desc, ind.is_unique, ind.ignore_dup_key,
                      ind.fill_factor, ind.is_padded, ind.allow_row_locks,
                      ind.allow_page_locks, col.NAME AS column_nName,
                      ind_col.key_ordinal, ind_col.is_descending_key
                  FROM   sys.indexes ind
                         LEFT OUTER JOIN sys.stats sta ON sta.object_id = ind.object_id
                                                       AND sta.stats_id = ind.index_id
                         LEFT OUTER JOIN (sys.index_columns ind_col
                         INNER JOIN sys.columns col ON col.object_id = ind_col.object_id
                                                  AND col.column_id = ind_col.column_id
                            ) ON ind_col.object_id = ind.object_id
                                 AND ind_col.index_id = ind.index_id
                         LEFT OUTER JOIN sys.data_spaces dsp 
                                 ON dsp.data_space_id = ind.data_space_id
                         INNER JOIN sys.tables st ON ind.object_id = st.object_id
                  WHERE  st.NAME = @my_Table
                            AND ind.index_id >= 0
                            AND ind.is_disabled = 0
                            AND ind.is_primary_key = 0
                            AND ind.type <> 3
                            AND ind.type <> 4
                            AND ind.is_hypothetical = 0),
                            bigQ (indName, cols, isUnique, type, options)
             AS (
                SELECT DISTINCT NAME COLLATE DATABASE_DEFAULT,(
                               SELECT column_nName + CASE key_desc
                                           WHEN 1 THEN ' DESC' ELSE '' END + ','
                               FROM   qry q2
                               WHERE  q2.NAME = q1.NAME
                       ORDER BY NAME, key_ord
                       FOR XML PATH('')), is_unique, type_desc,  
                                       'DROP_EXISTING=ON,IGNORE_DUP_KEY=' +
                    CASE ignore_dup_key
                       WHEN 1 THEN 'ON' ELSE 'OFF'
                       END + ',FILLFACTOR=' + STR(CASE WHEN fill_factor>0
                                                       THEN fill_factor
                                                       ELSE 79 END, 3, 0) +
                             ',PAD_INDEX=' + CASE pad_index
                       WHEN 1 THEN 'ON' ELSE 'OFF'
                       END + ',ALLOW_ROW_LOCKS=' + CASE row_locks
                       WHEN 1 THEN 'ON' ELSE 'OFF'
                       END + ',ALLOW_PAGE_LOCKS=' + CASE page_locks
                       WHEN 1 THEN 'ON' ELSE 'OFF'END
       FROM qry q1
       )
       INSERT INTO #tmp_Indxs (execStr)
    SELECT 'CREATE ' + CASE isUnique
              WHEN 1
                     THEN 'UNIQUE '
              ELSE ''
              END + CASE type
              WHEN 'HEAP'
                     THEN NULL
              ELSE type
              END + ' INDEX [' + indName + '] ON [' + @my_Table + '] (' +
                     LEFT(cols, LEN(cols) - 1) + ') WITH (' + options + ')'
      FROM bigQ;


    SET @MaxRows = (SELECT COUNT(*)
              FROM #tmp_Indxs
              );

    WHILE @intRow_Cnt <= @MaxRows
              BEGIN
                     SELECT @execStr = execStr
                     FROM #tmp_Indxs
                     WHERE rnum = @intRow_Cnt;

                     IF @execStr IS NOT NULL
                           EXEC (@execStr);

                     SET @intRow_Cnt = @intRow_Cnt + 1;
              END


       SELECT TOP 1 @curPk = NAME
       FROM sys.indexes
       WHERE object_id = object_id(@my_Table)
              AND is_primary_key = 1;
             
      
    EXEC('IF OBJECT_ID('''+ @p_DESTINATIONTBL+''') IS NOT NULL BEGIN DROP TABLE '+ @p_DESTINATIONTBL +' END');
   
       IF @p_STRUCONLY = 1
          BEGIN
              EXEC('SELECT * INTO '+ @p_DESTINATIONTBL + ' FROM '+ @my_Table + ' WHERE 1=2');
          END
       Else
          BEGIN
              EXEC('SELECT * INTO '+ @p_DESTINATIONTBL + ' FROM '+ @my_Table); 
       END
       SELECT @execPK = coalesce(@execPK + ',', 'ALTER TABLE [' + @p_DESTINATIONTBL + ']
        ADD CONSTRAINT [my_EMP1'  +ind.NAME + '] PRIMARY KEY (') + '[' + col.NAME + ']'
       FROM sys.indexes ind
       LEFT OUTER JOIN (
       sys.index_columns ind_col INNER JOIN sys.columns col 
                         ON col.object_id = ind_col.object_id
                     AND col.column_id = ind_col.column_id
              ) ON ind_col.object_id = ind.object_id
              AND ind_col.index_id = ind.index_id
       WHERE ind.object_id = object_id(@my_Table)
              AND is_primary_key = 1
       ORDER BY ind.index_id
              ,ind_col.key_ordinal;

       SET @execPK = @execPK + ');';
      
       IF @execPK IS NOT NULL
              EXEC (@execPK);
             
    PRINT 'Table Copied';
    END TRY
    BEGIN CATCH
      PRINT 'Error Found';
    END CATCH       
END      
To execute this stored procedure
EXEC my_TBLCOPY
     @p_STRUCONLY      = 1,
     @p_SOURCETBL      = 'my_EMPTABLE',
     @p_DESTINATIONTBL = 'my_EMPTABLE_CPY'

Here    
@p_STRUCONLY: When the Value of this parameter is 1 it only copy the structure not the data. If other then 1 it copies structure with data.
@p_SOURCETBL: The name of the source table.
@p_DESTINATIONTBL: The name of the Destination Table.

Please try this stored procedure. I hope you like it.



Posted by: MR. JOYDEEP DAS