SQL Server delete all tables under special schema with temporal tables

Question

I'm trying to delete a database scheme with temporal tables. Non of the existing scripts found through googling, supports temporal tables.

Is there anyone already done this?

There are many temporal tables on that scheme with many constraints with dependencies. so when I try to drop the scheme it complain about dependencies. Basically I'm looking for a stored procedure or something that go through all the DB objects and remove one by one.

Script to Create sample tables

USE [master];
GO

CREATE DATABASE [TestDb];
GO

USE [TestDb];
GO

CREATE SCHEMA [TestScheme];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [TestScheme].[Country]
(
    [CountryCode] [char](2) NOT NULL, 
    [Country] [varchar](60) NOT NULL, 
    [ValidFrom] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL, 
    [ValidTo] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL, 

    CONSTRAINT [PK_TestScheme_Country_CountryCode] 
        PRIMARY KEY CLUSTERED([CountryCode] ASC) 
                WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY], 
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
) ON [PRIMARY] 
     WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [TestScheme].[CountryHistory]));
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [TestScheme].[Address]
(
     [AddressId] [int] IDENTITY(1, 1) NOT NULL, 
     [City] [varchar](100) NOT NULL, 
     [CountryCode] [char](2) NOT NULL, 
     [ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL, 
     [ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL, 

     CONSTRAINT [PK_TestScheme_Address_AddressId] 
         PRIMARY KEY CLUSTERED([AddressId] ASC) 
                 WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, 
                       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                       ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY], 
     PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
ON [PRIMARY] 
   WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = [TestScheme].[AddressHistory]));
GO

ALTER TABLE [TestScheme].[Address] WITH CHECK
    ADD CONSTRAINT [FK_TestScheme_CountryCode] 
        FOREIGN KEY([CountryCode]) REFERENCES [TestScheme].[Country]([CountryCode]);
GO

ALTER TABLE [TestScheme].[Address] CHECK CONSTRAINT [FK_TestScheme_CountryCode];
GO

Query to drop scheme:

USE [TestDb];
GO

DROP SCHEMA [TestScheme];
GO

Query to delete table:

USE [TestDb]
GO

ALTER TABLE [TestScheme].[Country] SET (SYSTEM_VERSIONING = OFF)
GO

IF EXISTS (SELECT * FROM sys.objects 
           WHERE object_id = OBJECT_ID(N'[TestScheme].[Country]') AND type in (N'U'))
    DROP TABLE [TestScheme].[Country]
GO

IF EXISTS (SELECT * FROM sys.objects 
           WHERE object_id = OBJECT_ID(N'[TestScheme].[CountryHistory]') AND type in (N'U'))
    DROP TABLE [TestScheme].[CountryHistory]
GO

So the problem is there are many DB objects that I really don't want to create a huge script to delete one by one.

Thanks!

Answer 1

Thanks every one, following is the script I created and it worked for me.

USE TestDb;
GO
DECLARE @SchemeName varchar(50)= 'TestScheme';
DECLARE @DatabaseName varchar(50)= 'TestDb';
DECLARE @sql nvarchar(max)= '';

/*Removing versioning on temporal tables*/
WITH selectedTables
     AS (SELECT concat('[', @DatabaseName, '].[', @SchemeName, '].[', name, ']') AS TableName
         FROM SYS.TABLES    WHERE history_table_id IS NOT NULL AND  SCHEMA_NAME(schema_id) = @SchemeName)
     SELECT @sql = COALESCE(@sql, N'') + 'ALTER TABLE   ' + TableName + '   SET ( SYSTEM_VERSIONING = OFF  );'
     FROM selectedTables;

SELECT @sql;
EXEC sp_executesql @sql;

/*Remove constraints*/
SET @sql = N'';
SELECT @sql = COALESCE(@sql, N'') + N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' DROP CONSTRAINT ' + QUOTENAME(c.name) + ';'
FROM SYS.OBJECTS AS c   INNER JOIN  SYS.TABLES AS t ON c.parent_object_id = t.[object_id]
     INNER JOIN SYS.SCHEMAS AS s    ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN( 'D', 'C', 'F', 'PK', 'UQ' ) AND  s.name = @SchemeName
ORDER BY c.[type];

SELECT @sql;
EXEC sp_executesql @sql;

/*Delete Tables*/
SET @sql = N'';
SELECT @sql =   COALESCE(@sql, N'') + N'DROP TABLE ['+@SchemeName+'].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = @SchemeName    AND TABLE_TYPE = 'BASE TABLE';

SELECT @sql
EXEC sp_executesql @sql;

/*Drop scheme*/
SET @sql = N'';
SELECT @sql =   COALESCE(@sql, N'') + N'DROP SCHEMA IF EXISTS ' + @SchemeName + ';' + CHAR(13);

SELECT @sql
EXEC sp_executesql @sql;
GO

Thanks again!