SQL Server 2008 delete all tables under special schema

Question

Hello I would like to know is is possible to drop all tables in database what was created under custom schema for example DBO1...with one sql query or special script.

Thanks

Answer 1

This will generate all the DROP TABLE statements for you and PRINT the SQL statement out. You can then validate it's what you expect before copying and executing. Just make sure you are 100% sure...maybe take a backup first :)

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

Somewhat old thread I know, but I was looking for something like this and found the original answer very helpful. That said, the script will also try to drop views that might exist in that schema and give you an error message because you end up trying to drop a view by issuing a DROP TABLE statement.

I ended up writing this because I needed to drop all tables, views, procedures and functions from a given schema. Maybe not the most elegant way to accomplish this, but it worked for me and I thought I'd share.

DECLARE @Sql VARCHAR(MAX)       , @Schema varchar(20)  SET @Schema = 'Integration' --put your schema name between these quotes  --tables SELECT @Sql = COALESCE(@Sql,'') + 'DROP TABLE %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 = COALESCE(@Sql,'') + 'DROP VIEW %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 = COALESCE(@Sql,'') + 'DROP PROCEDURE %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 = COALESCE(@Sql,'') + 'DROP FUNCTION %SCHEMA%.' + QUOTENAME(ROUTINE_NAME) + ';' + CHAR(13) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = @Schema     AND ROUTINE_TYPE = 'FUNCTION' ORDER BY ROUTINE_NAME   SELECT @Sql = COALESCE(REPLACE(@Sql,'%SCHEMA%',@Schema), '')  PRINT @Sql