Script: Dropping Temporal Tables

Temporal tables are awesome. But they're kind of a pain to work with during those initial stages of development when you're constantly dropping and re-creating objects whole. The process of dropping a temporal table isn't super complicated, just:

1. Disable versioning on the main table
2. Drop the main table
3. Drop the history table

To that end, I wrote this stored procedure, marked as a system object (using sp_msmarksystemobject) which you can build which will do those three tasks for you automatically. Even if you don't choose to use the full script, you can still de-construct it for the logic.

use master
go
set nocount on
go

/*****************************
PROC: dbo.sp_droptemporaltable

create table Util.dbo.Test
(
 ID int primary key clustered,
 StartDate datetime2 generated always as row start,
 EndDate datetime2 generated always as row end,
 period for system_time (StartDate, EndDate)
)
with (system_versioning = on (history_table = dbo.Test_History))

exec Util.dbo.sp_droptemporaltable
 @SchemaTable = 'dbo.Test',
 @DropHistoryTable =1,
 @Debug = 0

*****************************/
drop proc if exists dbo.sp_droptemporaltable
go

create proc dbo.sp_droptemporaltable
 @SchemaTable nvarchar(300),
 @DropHistoryTable bit = 1,
 @Debug bit = 1
as
begin

declare 
 @ObjectId int = object_id(@SchemaTable),
 @TableName nvarchar(128) = parsename(@SchemaTable, 1),
 @SchemaName nvarchar(128) = parsename(@SchemaTable, 2),
 @HistObjectId int,
 @HistTableName nvarchar(128),
 @HistSchemaName nvarchar(128),
 @HistTwoPartName nvarchar(300),
 @TwoPartName nvarchar(300),
 @SQL nvarchar(max)

begin try

 select 
  @HistObjectId = history_table_id,
  @HistSchemaName = object_schema_name(history_table_id),
  @HistTableName = object_name(history_table_id),
  @HistTwoPartName = concat(quotename(@HistSchemaName), '.', quotename(@HistTableName)),
  @TwoPartName = concat(quotename(@SchemaName), '.', quotename(@TableName))
 from sys.tables
 where object_id = @ObjectId
  and temporal_type = 2

 if @ObjectId is null
 begin
  raiserror('%s is not a valid table', 16, 1, @SchemaTable)
 end

 if @HistObjectId is null
 begin
  raiserror('No history table exists for %s', 16, 1, @SchemaTable)
 end

 select @SQL = concat('

  alter table ', @TwoPartName, '
   set (system_versioning = off)

  print ''Disabled system versioning on ', @TwoPartName, '''

  drop table ', @TwoPartName, '

  print ''Dropped table ', @TwoPartName, '''

  drop table ', @HistTwoPartName, '

  print ''Dropped history table ', @HistTwoPartName, '''')

 if @debug = 1
  print @SQL
 else
  exec sp_executesql @SQL

end try
begin catch

 if @@trancount > 0
  rollback

 ;throw

end catch
end
go

exec sp_MS_marksystemobject 'sp_droptemporaltable'