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 &lt;&gt; '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