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
