Hi Guys.
I did some Identation to the code, to ease its readability and understanding. 😎
Thanks, Vidyadhar!
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TRUNCATE_PARTITION]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TRUNCATE_PARTITION]
GO
CREATE PROCEDURE [dbo].[TRUNCATE_PARTITION]
/*
- Procedure Name: dbo.TRUNCATE_PARTITION
- Date of creation: 06-Jan-2010
- Author- Mr. Vidhaydhar Vijay Pandekar
- Email-
- Description: Truncates specified Partition from partitioned table.
- Application:
1. To truncate the partition automatically and to avoid sequence of manual steps required for truncating partitions
2. As a replacement to the ALTER TABLE TRUNCATE PARITION statement of oracle. This becomes useful when oracle code
requires replacement for this statement while migrating to SQL Server.
- Input Parameters:
1. @SchemaName - Partitioned Table Schema Name
2. @TabName - Partitioned Table Name
3. @PartitionNo - Partition number to be truncated
- Command for execution- exec TRUNCATE_PARTITION 'SchemaName','TableName',PartitionNumber
i.e. exe TRUNCATE_PARTITION 'dbo','Table1',3
- Successful Test results for-
1 No Clustered Primary key and No Clustered Index
2 Clustered Primary key
3 No Primary key and Clustered Index
4. Non Clustered Primary key
- Change History
v1.0 Creation - 06-Jan-2010
V2.0 Modied - 9th Feb-2010- Table Schema name issue resolved
V3.0 Modified- 10th Feb 2010 - step1.5 Added functionality to consider if source table/ partition is compressed????
v4.0 Modified-11th Feb 2010 - Step 2- modified Pk related issue
*/
(
@SchemaNameVARCHAR(20)
,@TabNameVARCHAR(100)
,@PartitionNoINT
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
/* Step-1 start create staging table*/
DECLARE @PkIndexVARCHAR(200)
,@NewTabVARCHAR(500)
,@CreateTab VARCHAR(8000);
SET@NewTab = @SchemaName+'.'+@TabName+'_'+CONVERT(VARCHAR(5),@PartitionNo);
SET @CreateTab ='SELECT TOP 0 *
INTO '+@NewTab+'
FROM '+@SchemaName+'.'+@TabName;
-- STEP 1.1
-- ADDED ON July 22, 2015. Added functionality to eventually drop existing temporary table.
IF OBJECT_ID(@NewTab) IS NOT NULL
EXEC ('DROP TABLE '+ @NewTab);
EXEC (@CreateTab);
--STEP 1.5
-- ADDED ON 10th Feb 2010. Added functionality to the script of source partitioned table/ partition is compressed.
DECLARE @IsCompressedINT
,@CompressionTypeVARCHAR(10)
,@altStatementVARCHAR(1000);
SELECT @IsCompressed= DATA_COMPRESSION
,@CompressionType= data_compression_desc
FROMsys.partitions
WHEREobject_id= OBJECT_ID(@SchemaName+'.'+@TabName)AND
partition_number= @PartitionNoAND
index_id= 0;
IF @IsCompressed = 1
BEGIN
SET @altStatement = 'ALTER TABLE '+@NewTab+'
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = '+@CompressionType+')';
EXEC (@altStatement);
END;
/*Step2-start add PK */
DECLARE @Pk_available INT =0
,@CI_available INT =0;
SELECT @Pk_available = (
SELECT1
FROMsys.objects a
INNER JOINsys.indexes b ON a.object_id = b.object_id
WHEREa.object_id= OBJECT_ID(@SchemaName+'.'+@TabName)AND
b.is_primary_key= 1AND
b.index_id= 1
);
SELECT @CI_available = (
SELECT1
FROMsys.objects a
INNER JOINsys.indexes b ON a.object_id = b.object_id
WHEREa.object_id = OBJECT_ID(@SchemaName+'.'+@TabName)AND
b.is_primary_key= 0AND
b.index_id= 1
);
--added on 11th Feb 2010
IF @Pk_available IS NULL SET @Pk_available=0;
IF @CI_available IS NULL SET @CI_available=0;
------
IF (@Pk_available= '1' OR
@CI_available= '1')
BEGIN
DECLARE @TAB_ID1 int;
SELECT @TAB_ID1 = OBJECT_ID(@SchemaName+'.'+@TabName);
DECLARE @pkInfo TABLE(
schemaname VARCHAR(20)
,table_name VARCHAR(100)
,pk_nameVARCHAR(100)
,columnName VARCHAR(100)
,asckeyCHAR(1)
,IsUniqueCHAR(1)
);
INSERT INTO @pkInfo
(schemaname
,table_name
,pk_name
,columnName
,asckey
,IsUnique)
SELECT SCHEMANAME = @SchemaName
,B.NAMETABLE_NAME
,PK_NAME= ( SELECTa.name PK_NAME
FROMsys.indexes a
WHEREa.object_id= b.object_id AND
A.index_id= 1)
,COLUMN_NAME= ( SELECTname
FROMsys.columns E
WHEREE.OBJECT_ID = B.object_id AND
E.column_id= D.column_id)
,D.is_descending_key
,C.is_unique
FROMsys.objects B
INNER JOINsys.indexes C
ONB.object_id = C.object_id
INNER JOINsys.index_columns D
ON B.object_id = D.object_id
WHEREb.type= 'U'AND
C.index_id= 1AND
B.object_id= @TAB_ID1;
DECLARE @alterstatementVARCHAR(8000)
,@PknameVARCHAR(100)
,@columnsVARCHAR(4000);
SELECT @Pkname = pk_name FROM @pkInfo;
DECLARE @ALLcolumns TABLE(
idcol1INT IDENTITY
,colnameVARCHAR(100)
);
INSERT INTO @ALLcolumns (colname) SELECT columnName FROM @pkInfo;
DECLARE @cntINT
,@clncntINT;
SELECT @cnt=1;
SELECT @clncnt = COUNT(*) FROM @ALLcolumns;
DECLARE @cols VARCHAR(400);
SELECT @cols='';
WHILE @clncnt >= @cnt
BEGIN
SELECT@cols = @cols+','+ colname
FROM@ALLcolumns
WHEREidcol1 = @cnt;
SET @cnt=@cnt+1;
END;
SET @columns = SUBSTRING(@cols,2,len(@cols));
END;
IF @Pk_available = '1'
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+ '
ADD CONSTRAINT '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
PRIMARY KEY CLUSTERED ('+@columns+')';
IF @Pk_available <> '1'
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+'
ADD IDCOL INT
CONSTRAINT PK_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo) +' PRIMARY KEY';
EXEC (@alterstatement);
/* Step3- Start identify file group of partition to be truncated*/
DECLARE @filegroup VARCHAR(50);
SELECT@filegroup = CASE
WHEN fg.name IS NULL THEN ds.name
ELSE fg.name
END
FROMsys.dm_db_partition_statsp
INNER JOINsys.indexesi
ONi.object_id= p.object_idAND
i.index_id= p.index_id
INNER JOINsys.data_spacesds
ON ds.data_space_id= i.data_space_id
LEFT OUTER
JOINsys.partition_schemesps
ONps.data_space_id= i.data_space_id
LEFT OUTER
JOINsys.destination_data_spaces dds
ONdds.partition_scheme_id = ps.data_space_idAND
dds.destination_id= p.partition_number
LEFT OUTER
JOINsys.filegroupsfg
ONfg.data_space_id= dds.data_space_id
LEFT OUTER
JOINsys.partition_range_valuesprv_right
ONprv_right.function_id= ps.function_idAND
prv_right.boundary_id= p.partition_number
LEFT OUTER
JOINsys.partition_range_valuesprv_left
ONprv_left.function_id= ps.function_idAND
prv_left.boundary_id= p.partition_number - 1
WHERE
OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped')=0AND
p.index_idIN (0,1)AND
OBJECT_NAME(p.OBJECT_ID)=@TabNameAND
p.partition_number=@PartitionNo;
/*Step4- Start Move table to File group of Partition Table */
IF (@Pk_available='1' )
BEGIN
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+ '
DROP CONSTRAINT '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
WITH (MOVE TO ['+@filegroup+'])';
EXEC (@alterstatement);
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+ '
ADD CONSTRAINT '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
PRIMARY KEY ( '+@columns+')';
EXEC (@alterstatement);
END;
IF (@Pk_available<>'1' )
BEGIN
SELECT @alterstatement = 'ALTER TABLE '+@NewTab+ '
DROP CONSTRAINT PK_'+@TabName+'_'+CONVERT(varchar(5),@PartitionNo) +'
WITH (MOVE TO ['+@filegroup+'])';
EXEC (@alterstatement);
SELECT @alterstatement='ALTER TABLE '+@NewTab+ '
DROP COLUMN IDCOL';
EXEC (@alterstatement);
END;
/*Step5- Create clustered index of staging table if it is there on source partitioned table to make the schema equal */
IF (@CI_available='1' )
BEGIN
DECLARE @IsUnique char(1);
SELECT@IsUnique = IsUnique
FROM@pkInfo;
IF(@CI_available= '1' AND
@IsUnique= '1')
SELECT @alterstatement = 'CREATE UNIQUE CLUSTERED INDEX '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
ON '+ @NewTab+ ' ( '+@columns+')';
IF (@CI_available= '1' AND
@IsUnique= '0')
SELECT @alterstatement = 'CREATE CLUSTERED INDEX '+@Pkname+CONVERT(varchar(5),@PartitionNo)+'
ON '+ @NewTab+ ' ( '+@columns+')'
EXEC (@alterstatement);
END;
--Step6 - switch partition
SELECT @alterstatement = 'ALTER TABLE '+@SCHEMANAME+'.'+@TabName+'
SWITCH PARTITION '+CONVERT(varchar(5),@PartitionNo)+'
TO '+@NewTab;
EXEC (@alterstatement);
--Step7 drop staging table
SELECT @alterstatement = 'DROP TABLE '+@NewTab;
EXEC (@alterstatement);
PRINT 'trunc com sucesso!';
END TRY
--Error Handling
BEGIN CATCH
PRINT 'Truncate Partition Failed due to error.';
SELECT
ERROR_NUMBER()AS ErrorNumber
,ERROR_SEVERITY()AS ErrorSeverity
,ERROR_STATE()AS ErrorState
,ERROR_PROCEDURE()AS ErrorProcedure
,ERROR_LINE()AS ErrorLine
,ERROR_MESSAGE()AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END;
GO
Just to say, I successfully use it in my automated ETL routines everyday. 😎
- Thiago (SP)