Trjtdtk

Quoting Keynes in a lecture

Doesn't the system of the Supreme Court oppose justice?

What's the name of the logical fallacy where a debater extends a statement far beyond the original statement to make it true?

Devil Fruit Question

I found an audio circuit and I built it just fine, but I find it a bit too quiet. How do I amplify the output so that it is a bit louder?

Will the Sticky MAC access policy prevent unauthorized hubs from connecting to a network?

A variation to the phrase "hanging over my shoulders"

Why should universal income be universal?

How could a planet have erratic days?

Shouldn’t conservatives embrace universal basic income?

A Trivial Diagnosis

Unable to get dependencies from jcenter with a new project

What fields between the rationals and the reals allow a good notion of 2D distance?

What is going on with gets(stdin) on the site coderbyte?

Does grappling negate Mirror Image?

Which was the first story featuring espers?

Does Doodling or Improvising on the Piano Have Any Benefits?

Is this toilet slogan correct usage of the English language?

Boundary Value Problem and FullSimplify

Why is it that I can sometimes guess the next note?

Mimic lecturing on blackboard, facing audience

Why do ¬, ∀ and ∃ have the same precedence?

Does the reader need to like the PoV character?

Merge org tables

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?

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 yesterday

bege1bege1

333

New contributor

bege1 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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 yesterday

bege1bege1

333

New contributor

bege1 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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 yesterday

bege1bege1

333

New contributor

bege1 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked yesterday

bege1bege1

333

New contributor

bege1 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked yesterday

bege1bege1

333

New contributor

bege1 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked yesterday

bege1bege1

333

New contributor

bege1 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked yesterday

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 yesterday

DanDan

734417

• 
  
  
  

  
  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
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @bege1, did that worked for you?
  
  – Dan
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is working perfectly!!!!! Thanks for sharing @Dan
  
  – bege1
  6 hours ago
  

  
  
  
  

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
);



);

bege1 is a new contributor. Be nice, and check out our Code of Conduct.

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 yesterday

DanDan

734417

• 
  
  
  

  
  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
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @bege1, did that worked for you?
  
  – Dan
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is working perfectly!!!!! Thanks for sharing @Dan
  
  – bege1
  6 hours ago
  

  
  
  
  

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 yesterday

DanDan

734417

• 
  
  
  

  
  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
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @bege1, did that worked for you?
  
  – Dan
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is working perfectly!!!!! Thanks for sharing @Dan
  
  – bege1
  6 hours ago
  

  
  
  
  

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 yesterday

DanDan

734417

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 yesterday

DanDan

734417

answered yesterday

DanDan

734417

answered yesterday

DanDan

734417