Duplicate Sql Server Table

• Tweet
• 

        <b>Purpose</b>
This code will duplicate a sql server table with all the primary keys, default constraints etc in tact

Foreign keys are not yet done.

The proceedure is going to be mainly used to duplicate a set of tables, adding a prefix to the table name

DECLARE @tableName varchar(255);
DECLARE @user_name varchar(255);
DECLARE @tmpColumnName varchar(255);
DECLARE @tmpConstraintName varchar(255);
DECLARE @tmpDefaultClause varchar(255);
DECLARE @sqlString varchar(5000);
DECLARE @dfIndex INT;
DECLARE @tmpIndex INT;
DECLARE @newTablePrefix varchar(50);
DECLARE @tableLikeString varchar(255);

SET @tableLikeString = 'cpoCGPA%'; --table names to duplicate (fks will be between new tables if they are there)
SET @newTablePrefix = 'zzz'; --dummy prefix

DECLARE [tableList] CURSOR FOR (
	select name, user_name(uid) from sysobjects 
	where type='U'
	AND (name LIKE @tableLikeString )
)
OPEN tableList
FETCH tableList INTO @tableName, @user_name
WHILE @@Fetch_Status = 0
	BEGIN
	
		--create Table
		SET @sqlString = 'SELECT * INTO ';
		SET @sqlString = @sqlString + @newTablePrefix + @tableName;
		SET @sqlString = @sqlString + ' FROM ' + @tableName + ' WHERE 1=0';
		EXEC( @sqlString );
		
		--default constraints
		DECLARE constraintList CURSOR FOR (
			select	col.name			as COLUMN_NAME
				,com.text				as DEFAULT_CLAUSE
				,col.colid				AS ordinal_position

			from	sysobjects	c_obj
			join 	syscomments	com on 	c_obj.id = com.id
			join 	sysobjects	t_obj on c_obj.parent_obj = t_obj.id  
			join    sysconstraints con on c_obj.id	= con.constid
			join 	syscolumns	col on t_obj.id = col.id
						and con.colid = col.colid
			where
				c_obj.uid	= user_id()
				and c_obj.xtype	= 'D'
				and t_obj.name  = @tableName
		)
		OPEN constraintList
		FETCH constraintList into @tmpColumnName, @tmpDefaultClause, @dfIndex
		WHILE @@Fetch_Status = 0
			BEGIN
				SET @sqlString = 'ALTER TABLE ' + @newTablePrefix + @tableName + ' ADD CONSTRAINT [DF_';
				SET @sqlString = @sqlString + @newTablePrefix + @tableName + '_' + @tmpColumnName + '_';
				SET @sqlString = @sqlString + CAST(  @dfIndex AS VARCHAR ) + ']';
				SET @sqlString = @sqlString + ' DEFAULT ' + @tmpDefaultClause + ' FOR [' + @tmpColumnName + ']';
				
				EXEC( @sqlString )
			FETCH constraintList into @tmpColumnName, @tmpDefaultClause, @dfIndex
			END
		CLOSE constraintList
		DEALLOCATE constraintList
		
		--primary Key Constraints
		DECLARE constraintNameList CURSOR FOR (
			SELECT DISTINCT constraint_name, MIN(ORDINAL_POSITION)
			FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
			WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
			AND table_name = @tableName
			GROUP BY constraint_name
		)
		OPEN constraintNameList
		FETCH constraintNameList INTO @tmpConstraintName, @dfIndex
		WHILE @@Fetch_Status = 0
			BEGIN
			-- build the constraint string
			SET @sqlString = 'ALTER TABLE ' + @newTablePrefix + @tableName + ' ADD CONSTRAINT [PK_';
			SET @sqlString = @sqlString + @newTablePrefix + @tableName + '_';
			SET @sqlString = @sqlString + CAST(  @dfIndex AS VARCHAR ) + '] PRIMARY KEY NONCLUSTERED (';
			
			SET @tmpIndex = 0;
			--get the columns in this constraint
			DECLARE constraintColumnList CURSOR FOR (
				SELECT column_name
				FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
				WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
				AND table_name = @tableName
			)
			OPEN constraintColumnList
			FETCH constraintColumnList INTO @tmpColumnName
			WHILE @@Fetch_Status = 0
				BEGIN
				IF @tmpIndex > 0
				BEGIN
					SET @sqlString = @sqlString + ', '
				END
				SET @sqlString = @sqlString + '[' + @tmpColumnName + ']';
				SET @tmpIndex = @tmpIndex + 1;
				
				FETCH constraintColumnList INTO @tmpColumnName
				END
			CLOSE constraintColumnList
			DEALLOCATE constraintColumnList
			
			SET @sqlString = @sqlString + ') WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]';
			EXEC ( @sqlString );

			FETCH constraintNameList INTO @tmpConstraintName, @dfIndex
			END
		CLOSE constraintNameList
		DEALLOCATE constraintNameList
		
		
	FETCH tableList INTO @tableName, @user_name
	END
CLOSE tableList

DECLARE @constraintTableTo VARCHAR(255);
DECLARE @constraintColumnFrom VARCHAR(255);
DECLARE @constraintColumnTo VARCHAR(255);

OPEN tableList
FETCH tableList INTO @tableName, @user_name
WHILE @@Fetch_Status = 0
	BEGIN
	
	DECLARE fkConstraintTable CURSOR FOR (
		SELECT CU.COLUMN_NAME, PK.TABLE_NAME, PT.COLUMN_NAME
		FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
		INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
		INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
		INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
		INNER JOIN (
		SELECT i1.TABLE_NAME, i2.COLUMN_NAME
		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
		INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
		WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
		) PT ON PT.TABLE_NAME = PK.TABLE_NAME
		WHERE FK.TABLE_NAME = @tableName
	)
	OPEN fkConstraintTable
	FETCH fkConstraintTable INTO @constraintColumnFrom, @constraintTableTo, @constraintColumnTo
	WHILE @@Fetch_Status = 0
		BEGIN
			IF (
				EXISTS( SELECT 1 FROM (SELECT 1 a) a WHERE @constraintTableTo LIKE @tableLikeString ) AND --fk is to like table
				EXISTS( SELECT 1 from sysobjects where type='U' AND NAME = @newTablePrefix + @constraintTableTo ) -- check new table exists
			)
			BEGIN
				SET @constraintTableTo = @newTablePrefix + @constraintTableTo;
			END
		
			SET @sqlString = 'ALTER TABLE ' + @newTablePrefix + @tableName + ' WITH CHECK ADD CONSTRAINT [FK_'; --constraint
			SET @sqlString = @sqlString + @newTablePrefix + @tableName + '.' + @constraintColumnFrom; -- from
			SET @sqlString = @sqlString + '_' + @constraintTableTo + '.' + @constraintColumnTo + ']'; -- to
			SET @sqlString = @sqlString + ' FOREIGN KEY ([' + @constraintColumnFrom + '])'; --constraint from
			SET @sqlString = @sqlString + ' REFERENCES [' + @constraintTableTo + '] ([' + @constraintColumnTo + '])';
			
			EXEC ( @sqlString )
				
		FETCH fkConstraintTable INTO @constraintColumnFrom, @constraintTableTo, @constraintColumnTo
		END
	CLOSE fkConstraintTable
	DEALLOCATE fkConstraintTable
		
	FETCH tableList INTO @tableName, @user_name
	END
CLOSE tableList
DEALLOCATE tableList

Tags:
• SQL