dbo.usp_TruncateTables - P

type: P ( stored procedure ), modify_date: 2022-01-13 12:23:17

RepoObject_guid: 1CACB147-4AF5-EB11-850C-A81E8446D5B0

Description

  • truncate tables in a given schema

Examples

Example 1. Usage
EXEC [dbo].[usp_TruncateTables]
@Schema = 'MySchema'

Parameters

  • @Schema (nvarchar(128))

Entity Diagram

entity-dbo.usp_truncatetables

References

Object Reference Diagram - 1 1

entity_1_1_objectref-dbo.usp_truncatetables

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-dbo.usp_truncatetables

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-dbo.usp_truncatetables

sql_modules_definition

dbo.usp_TruncateTables - P script
/*
<<property_start>>Description
* truncate tables in a given schema
<<property_end>>

<<property_start>>exampleUsage
EXEC [dbo].[usp_TruncateTables]
@Schema = 'MySchema'
<<property_end>>
*/
CREATE Procedure [dbo].[usp_TruncateTables]
( @Schema NVarchar(128) Null )
As
Declare @Table_Schema Varchar(128);
Declare @Table_Name Varchar(128);
Declare @Table_Name_Full Varchar(300);
Declare @cmd As NVarchar(4000);

If @Schema Is Not Null
Begin
    Declare cur Cursor For
    Select
        TABLE_SCHEMA
      , TABLE_NAME
    From
        INFORMATION_SCHEMA.TABLES
    Where
        TABLE_TYPE       = 'BASE TABLE'
        And TABLE_SCHEMA = @Schema;
End;

--else
--    begin
--    DECLARE cur CURSOR FOR
--    select Table_SCHEMA,Table_Name from [INFORMATION_SCHEMA].[TABLES]
--    end
Open cur;

Fetch Next From cur
Into
    @Table_Schema
  , @Table_Name;

While @@Fetch_Status = 0
Begin
    Set @cmd = N'';
    Set @Table_Name_Full = Concat ( QuoteName ( @Table_Schema ), '.', QuoteName ( @Table_Name ));
    Set @cmd = Concat ( 'TRUNCATE TABLE ', @Table_Name_Full );

    Exec sys.sp_executesql @cmd;

    Print Concat ( @cmd, '    Executed' );

    Fetch Next From cur
    Into
        @Table_Schema
      , @Table_Name;
End;

Close cur;
Deallocate cur;