SQL Server 2008 supprime toutes les tables sous un schéma spécial

Question

Bonjour, je voudrais savoir s'il est possible de supprimer toutes les tables de la base de données créées sous un schéma personnalisé, par exemple DBO1 ... avec une requête SQL ou un script spécial.

Merci

Answer 1

Cela générera toutes les instructions DROP TABLE pour vous et IMPRIMERa l'instruction SQL. Vous pouvez ensuite valider que c'est ce que vous attendez avant de copier et d'exécuter. Assurez-vous simplement d'être sûr à 100%... peut-être faire une sauvegarde d'abord :)

 DECLARE @SqlStatement NVARCHAR(MAX)
SELECT @SqlStatement = 
    COALESCE(@SqlStatement, N'') + N'DROP TABLE [DBO1].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DBO1' and TABLE_TYPE = 'BASE TABLE'

PRINT @SqlStatement

Answer 2

Sur la base de la réponse de @Kevo, j'ai ajouté ce qui suit pour supprimer toutes les contraintes de clé étrangère avant de supprimer les tables. J'ai testé uniquement sur SQL2008 R2

 select @Sql = COALESCE(@Sql,'') + 'ALTER TABLE %SCHEMA%.' + t.name + ' drop constraint ' + 
OBJECT_NAME(d.constraint_object_id)  + ';' + CHAR(13)
from sys.tables t 
    join sys.foreign_key_columns d on d.parent_object_id = t.object_id 
    inner join sys.schemas s on t.schema_id = s.schema_id
where s.name = @Schema
ORDER BY t.name;

Answer 3

S'appuyant sur les autres réponses, voici une procédure stockée spDropSchema qui supprime tous les objets d'un schéma et le schéma lui-même.

Notez que la procédure essaie également de supprimer les objets de séquence, elle ne fonctionnera donc que sur SQL Server 2012 et versions ultérieures.

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spDropSchema')
    BEGIN
        DROP  PROCEDURE  spDropSchema
    END
GO

CREATE PROCEDURE spDropSchema(@Schema nvarchar(200))
AS

DECLARE @Sql NVARCHAR(MAX) = '';

--constraints
SELECT @Sql = @Sql + 'ALTER TABLE '+ QUOTENAME(@Schema) + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + QUOTENAME(f.name)  + ';' + CHAR(13)
FROM sys.tables t 
    inner join sys.foreign_keys f on f.parent_object_id = t.object_id 
    inner join sys.schemas s on t.schema_id = s.schema_id
WHERE s.name = @Schema
ORDER BY t.name;

--tables
SELECT @Sql = @Sql + 'DROP TABLE '+ QUOTENAME(@Schema) +'.' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @Schema AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

--views
SELECT @Sql = @Sql + 'DROP VIEW '+ QUOTENAME(@Schema) +'.' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @Schema AND TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME

--procedures
SELECT @Sql = @Sql + 'DROP PROCEDURE '+ QUOTENAME(@Schema) +'.' + QUOTENAME(ROUTINE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = @Schema AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME

--functions
SELECT @Sql = @Sql + 'DROP FUNCTION '+ QUOTENAME(@Schema) +'.' + QUOTENAME(ROUTINE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = @Schema AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

--sequences
SELECT @Sql = @Sql + 'DROP SEQUENCE '+ QUOTENAME(@Schema) +'.' + QUOTENAME(SEQUENCE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.SEQUENCES
WHERE SEQUENCE_SCHEMA = @Schema
ORDER BY SEQUENCE_NAME

--types
SELECT @Sql = @Sql + 'DROP TYPE ' + QUOTENAME(@Schema) + '.' + QUOTENAME(t.name) + ';' + CHAR(13)
FROM sys.types t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_user_defined = 1 AND
    s.name  = @Schema
ORDER BY s.name

SELECT @Sql = @Sql + 'DROP SCHEMA '+ QUOTENAME(@Schema) + ';' + CHAR(13)

EXECUTE sp_executesql @Sql

GO