How to Disable and Drop all Temporal Tables from a database
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
asked 2 hours ago
bege1bege1
311
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
asked 2 hours ago
bege1bege1
311
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
6
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
asked 2 hours ago
bege1bege1
311
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.
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
sql-server sql-server-2016
asked 2 hours ago
bege1bege1
311
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 2 hours ago
bege1bege1
311
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 2 hours ago
bege1bege1
311
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 2 hours ago
bege1bege1
311
asked 2 hours ago
bege1bege1
311
311
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.
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.
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 |
add a comment |
1 Answer
1
active
oldest
votes
5
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;
answered 2 hours ago
DanDan
704416
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
2 hours ago
@bege1, did that worked for you?
– Dan
2 hours ago
@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
2 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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
5
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;
answered 2 hours ago
DanDan
704416
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
2 hours ago
@bege1, did that worked for you?
– Dan
2 hours ago
@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
2 hours ago
add a comment |
5
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;
answered 2 hours ago
DanDan
704416
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
2 hours ago
@bege1, did that worked for you?
– Dan
2 hours ago
@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
2 hours ago
add a comment |
5
5
5
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;
answered 2 hours ago
DanDan
704416
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;
answered 2 hours ago
DanDan
704416
answered 2 hours ago
DanDan
704416
answered 2 hours ago
DanDan
704416
answered 2 hours ago
DanDan
704416
704416
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
2 hours ago
@bege1, did that worked for you?
– Dan
2 hours ago
@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
2 hours ago
add a comment |
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
2 hours ago
@bege1, did that worked for you?
– Dan
2 hours ago
@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
2 hours ago
1
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
2 hours ago
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
2 hours ago
@bege1, did that worked for you?
– Dan
2 hours ago
@bege1, did that worked for you?
– Dan
2 hours ago
@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
2 hours ago
@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
2 hours ago
add a comment |
bege1 is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
bege1 is a new contributor. Be nice, and check out our Code of Conduct.
bege1 is a new contributor. Be nice, and check out our Code of Conduct.
bege1 is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
