Ggtkuk

"The cow" OR "a cow" OR "cows" in this context

Why must Chinese maps be obfuscated?

How to fry ground beef so it is well-browned

How do I deal with a coworker that keeps asking to make small superficial changes to a report, and it is seriously triggering my anxiety?

Who was the lone kid in the line of people at the lake at the end of Avengers: Endgame?

Is there any official lore on the Far Realm?

Determine the application client is using to connect

What is the optimal strategy for the Dictionary Game?

"Whatever a Russian does, they end up making the Kalashnikov gun"? Are there any similar proverbs in English?

Usage of 万 (wàn): ten thousands or just a great number?

Pulling the rope with one hand is as heavy as with two hands?

Mistake in years of experience in resume?

constexpr member function with std::vector data member in C++

I preordered a game on my Xbox while on the home screen of my friend's account. Which of us owns the game?

Two field separators (colon and space) in awk

A ​Note ​on ​N!

What to do with someone that cheated their way through university and a PhD program?

A strange hotel

How do I check if a string is entirely made of the same substring?

Elements that can bond to themselves?

How could Tony Stark make this in Endgame?

What is the term for a person whose job is to place products on shelves in stores?

How to denote matrix elements succinctly

What makes accurate emulation of old systems a difficult task?

How to Disable and Drop all Temporal Tables from a database

DISABLE / DROP INDEX causing deadlockWill Temporal Tables be included in the Standard Edition of SQL Server 2016?Change the behavior of temporal tables to log actual value changes rather than dummy updatesRestore DB to older versionSimple way to Drop Test databases and restore from Prod databases on 2008r2Select TOP N Rows missing from SSMS temporal tableSQL Server 2016, temporal tables and compressed indexesTemporal tables: history table populationSQL Temporal Tables Include Current StateWhy do I have multiple (unassociated) temporal history tables?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

6

I'm working on a task where I need to backup and restore a database in another instance.

But, when I restore that database, I need to Drop all the History Tables associated with Temporal Tables.

Is there a quick way to do this? Any help is appreciated.

sql-server sql-server-2016

share|improve this question

asked Mar 20 at 15:39

bege1bege1

333

add a comment | 

6

I'm working on a task where I need to backup and restore a database in another instance.

But, when I restore that database, I need to Drop all the History Tables associated with Temporal Tables.

Is there a quick way to do this? Any help is appreciated.

sql-server sql-server-2016

share|improve this question

asked Mar 20 at 15:39

bege1bege1

333

add a comment | 

I'm working on a task where I need to backup and restore a database in another instance.

But, when I restore that database, I need to Drop all the History Tables associated with Temporal Tables.

Is there a quick way to do this? Any help is appreciated.

sql-server sql-server-2016

share|improve this question

asked Mar 20 at 15:39

bege1bege1

333

share|improve this question

asked Mar 20 at 15:39

bege1bege1

333

share|improve this question

asked Mar 20 at 15:39

bege1bege1

333

asked Mar 20 at 15:39

bege1bege1

333

asked Mar 20 at 15:39

bege1bege1

333

1 Answer
1

active

oldest

votes

6

I believe that's what you need.

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
 @SchemaName VARCHAR(100),
 @TableName VARCHAR(100),
 @HistorySchemaName VARCHAR(100),
 @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
 ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;


WHILE @@ROWCOUNT = 1
 BEGIN
 SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE ' + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
 EXEC sp_executesql @cmd

 SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
 FROM sys.tables T1
 LEFT JOIN sys.tables T2
 ON T1 .history_table_id = T2 .object_id
 WHERE
 T1 .temporal_type = 2
 AND T1 .name > @TableName
 ORDER BY T1 .name;
 END;

share|improve this answer

answered Mar 20 at 15:42

DanDan

869518

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Probably one of the fastest answers I've ever come across! You obviously must have had this script in your back pocket. Maybe worth providing some context just for others as they come across it.
  
  – John Eisbrener
  Mar 20 at 15:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @bege1, did that worked for you?
  
  – Dan
  Mar 20 at 15:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @John, yes indeed! I do have this script but wrapped inside of a stored procedure where I used to need to remove history data when moving database around in a environment with multi-tenant databases.
  
  – Dan
  Mar 20 at 15:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is working perfectly!!!!! Thanks for sharing @Dan
  
  – bege1
  Mar 21 at 14:52
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232624%2fhow-to-disable-and-drop-all-temporal-tables-from-a-database%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

6

I believe that's what you need.

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
 @SchemaName VARCHAR(100),
 @TableName VARCHAR(100),
 @HistorySchemaName VARCHAR(100),
 @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
 ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;


WHILE @@ROWCOUNT = 1
 BEGIN
 SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE ' + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
 EXEC sp_executesql @cmd

 SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
 FROM sys.tables T1
 LEFT JOIN sys.tables T2
 ON T1 .history_table_id = T2 .object_id
 WHERE
 T1 .temporal_type = 2
 AND T1 .name > @TableName
 ORDER BY T1 .name;
 END;

share|improve this answer

answered Mar 20 at 15:42

DanDan

869518

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Probably one of the fastest answers I've ever come across! You obviously must have had this script in your back pocket. Maybe worth providing some context just for others as they come across it.
  
  – John Eisbrener
  Mar 20 at 15:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @bege1, did that worked for you?
  
  – Dan
  Mar 20 at 15:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @John, yes indeed! I do have this script but wrapped inside of a stored procedure where I used to need to remove history data when moving database around in a environment with multi-tenant databases.
  
  – Dan
  Mar 20 at 15:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is working perfectly!!!!! Thanks for sharing @Dan
  
  – bege1
  Mar 21 at 14:52
  

  
  
  
  

add a comment | 

6

I believe that's what you need.

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
 @SchemaName VARCHAR(100),
 @TableName VARCHAR(100),
 @HistorySchemaName VARCHAR(100),
 @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
 ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;


WHILE @@ROWCOUNT = 1
 BEGIN
 SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE ' + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
 EXEC sp_executesql @cmd

 SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
 FROM sys.tables T1
 LEFT JOIN sys.tables T2
 ON T1 .history_table_id = T2 .object_id
 WHERE
 T1 .temporal_type = 2
 AND T1 .name > @TableName
 ORDER BY T1 .name;
 END;

share|improve this answer

answered Mar 20 at 15:42

DanDan

869518

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Probably one of the fastest answers I've ever come across! You obviously must have had this script in your back pocket. Maybe worth providing some context just for others as they come across it.
  
  – John Eisbrener
  Mar 20 at 15:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @bege1, did that worked for you?
  
  – Dan
  Mar 20 at 15:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @John, yes indeed! I do have this script but wrapped inside of a stored procedure where I used to need to remove history data when moving database around in a environment with multi-tenant databases.
  
  – Dan
  Mar 20 at 15:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is working perfectly!!!!! Thanks for sharing @Dan
  
  – bege1
  Mar 21 at 14:52
  

  
  
  
  

add a comment | 

I believe that's what you need.

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
 @SchemaName VARCHAR(100),
 @TableName VARCHAR(100),
 @HistorySchemaName VARCHAR(100),
 @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
 ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;


WHILE @@ROWCOUNT = 1
 BEGIN
 SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE ' + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
 EXEC sp_executesql @cmd

 SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
 FROM sys.tables T1
 LEFT JOIN sys.tables T2
 ON T1 .history_table_id = T2 .object_id
 WHERE
 T1 .temporal_type = 2
 AND T1 .name > @TableName
 ORDER BY T1 .name;
 END;

share|improve this answer

answered Mar 20 at 15:42

DanDan

869518

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
 @SchemaName VARCHAR(100),
 @TableName VARCHAR(100),
 @HistorySchemaName VARCHAR(100),
 @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
 ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;


WHILE @@ROWCOUNT = 1
 BEGIN
 SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE ' + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
 EXEC sp_executesql @cmd

 SELECT TOP ( 1 )
 @SchemaName = SCHEMA_NAME(T1.schema_id ),
 @TableName = T1 .name,
 @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
 @HistoryTableName = T2 .name
 FROM sys.tables T1
 LEFT JOIN sys.tables T2
 ON T1 .history_table_id = T2 .object_id
 WHERE
 T1 .temporal_type = 2
 AND T1 .name > @TableName
 ORDER BY T1 .name;
 END;

share|improve this answer

answered Mar 20 at 15:42

DanDan

869518

answered Mar 20 at 15:42

DanDan

869518

answered Mar 20 at 15:42

DanDan

869518