my code stock.com

Procedures which erases the entire database table's data.

Snippet options

Download: Download snippet as process-erasing.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

/****** Object:  StoredProcedure [dbo].[sp_processErasing]    Script Date: 04/12/2012 09:35:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* ***************************************************************************************************
 * Project Name:          	THOT
 * Project Version:      	$VersionInfo$
 * Creation Date:         	28 november 2011
 * Author:                	dfe
 * Classes Name:          	$ClassName$
 * General Description:	
Procedures which erases the entire database table's data.
 * References:            	$Reference$
 * ====================================================================================================
 * MODIFICATIONS
 * ----------------------------------------------------------------------------------------------------
 * Date            	Author        			Description			Project id
 * ----------------------------------------------------------------------------------------------------
 * Please complete after each code modification.
 * <date>          	<author>      			<fix id>			<documentation id>
 * <description of changes>
 * ************************************************************************************************* */
CREATE PROCEDURE [dbo].[sp_processErasing]
	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @DB_NAME nvarchar(50)
	DECLARE @PROC_NAME nvarchar(100)
	SELECT 
		@DB_NAME = DB_NAME()
	SELECT 
		@PROC_NAME = OBJECT_NAME(@@PROCID)
	
	PRINT('Entering ' + @PROC_NAME)

	BEGIN TRANSACTION processErasing
	BEGIN TRY
		-- disable all constraints
		PRINT ('Execute sp_processErasing -> disable constraint')
		EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

		-- delete data in all tables
		PRINT ('Execute sp_processErasing -> delete table')
		EXEC [sp_MSforeachtable] @command1='IF(''?'' <> ''[dbo].[sysdiagrams]'') DELETE FROM ?'

		-- enable all constraints
		PRINT ('Execute sp_processErasing -> re-enable constraint')
		EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

		COMMIT TRANSACTION processErasing
	END TRY
	BEGIN CATCH
		exec dbo.usp_RethrowError
		ROLLBACK TRANSACTION processErasing
	END CATCH
	
	PRINT('Exit ' + @PROC_NAME)
END

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Procedures which erases the entire database table's data.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'sp_processErasing'
GO

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.