Truncate all tables by Schema

As I  mainly work in development of SQL Server databases or data marts for data warehouses,  I quite often use schemas  (which are massively under utilised) in the database to separate out tables into sections of relative data.

After typing out TRUNCATE TABLE statements for numerous tables in a certain schema I decided it was time to speed up this process.

So I thought I’d share with people one stored procedure which I developed to simply truncate all tables in a certain schema (apart from dbo which would clearly be career suicide).  One example where I’ve found this script helpful was within an ETL package by removing the need to update a SQL task as you add tables which require truncating for whatever reason, this script makes this task more dynamic.

Use and manipulate the script as you wish, I just hope it helps someone, somewhere save some time.

CREATE PROC [dbo].[usp_TruncateTablesBySchema] (@Schema VARCHAR(15))
AS

/*=============================================
Author:DataDoor

DB Object: usp_TruncateStagingTables

Description:
This stored procedure is used to truncate all table in a specific schema.
=============================================*/

/*Declare required variables*/
DECLARE @SQLStatement NVARCHAR(MAX)

IF @Schema = 'dbo'
BEGIN

RAISERROR('This procedure should not be used on the schema dbo',16,1)

END

IF @Schema <> 'dbo'
BEGIN

/* BUILD TABLE TRUNCATION SQL*/

SELECT @SQLStatement = (
SELECT STUFF((SELECT '; ' +'TRUNCATE TABLE '+ [SchemaName] +'.'+[TableName] AS [text()]
FROM
( SELECT SCHEMA_NAME(SCHEMA_ID) AS [SchemaName] , name AS [TableName] FROM sys.tables WHERE SCHEMA_ID = SCHEMA_ID(@Schema)
) x
FOR XML PATH ('')),1,1,'')+';')

/*EXECUTE TRUNCATION SQL*/
EXEC sp_ExecuteSQL @stmt = @SQLStatement

END

GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s