SQL Server 2008 delete all tables under special schema

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

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

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

I know this is an old thread but I think the easiest way to do this is by using the undocumented sp_MSforeachtable stored procedure:

EXEC sp_MSforeachtable
  @command1 = 'DROP TABLE ?'
, @whereand = 'AND SCHEMA_NAME(schema_id) = ''your_schema_name'' '

A detailed report on this stored procedure can be found here, but in case the link goes dead here are the highlights:

> sp_MSforeachtable is a stored procedure that is mostly used to apply a T-SQL command to every table, iteratively, that exists in the current database.
[...]
realized that the question mark (?) it is used as the replacement of the table and during the execution it will be replaced by the appropriate table name.

> @command1, @command2, @command3
sp_MSforeachtable stored procedure requires at least one command to be executed (@command1) but it allows up to 3 commands to be executed. Note that it will start to execute first the @command1 and then @command2 and @command3 by the last and this for each table.

> @precommand
Use this parameter to provide a command to be executed before the @command1. It is useful to set variable environments or perform any kind of initialization.

> @postcommand
Use this parameter to provide a command to be executed after all the commands being executed successfully. It is useful for control and cleanup processes.

> @replacechar
By default, a table is represented by the question mark (?) character. This parameter allows you to change this character.

> @whereand
By default, sp_MSforeachtable is applied to all user tables in the database. Use this parameter to filter the tables that you want to work with. On the next section, I will explain how you can filter the tables.

Building on @Kevo's answer, I added the following for dropping all foreign key constraints before deleting the tables. I've only tested on 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;

Building on the other answers, here is a stored procedure spDropSchema that drops all objects in a schema and the schema itself.

Note that the procedure tries to drop sequence objects too, so it will only work on SQL Server 2012 and above.

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

Also building on @Kevo's answer, I added the following while loop for an issue I was having with TSQL Print statement. A message string can be up to 8,000 characters long. If greater than 8,000 the print statement will truncate any remaining characters.

DECLARE @SqlLength int
      , @SqlPosition int = 1
      , @printMaxLength int = 8000

SET @SqlLength = LEN(@Sql)

WHILE (@SqlLength) > @printMaxLength
BEGIN
	PRINT SUBSTRING(@Sql, @SqlPosition, @printMaxLength)
	SET @SqlLength = @SqlLength - @printMaxLength
	SET @SqlPosition = @SqlPosition + @printMaxLength
END
IF (@SqlLength) < @printMaxLength AND (@SqlLength) > 0
BEGIN
	PRINT SUBSTRING(@Sql, @SqlPosition, @printMaxLength)
END

I combined the answers from @raider33 and @Kevo to one solutions for direct execution.

DECLARE @SqlStatement NVARCHAR(MAX)
DECLARE @schema varchar(30) = 'SCHEMA_NAME';

select @SqlStatement = COALESCE(@SqlStatement,'') + 'ALTER TABLE '+@schema+'.' + t.name + ' drop constraint ' + 
OBJECT_NAME(d.constraint_object_id)  + ';' + CHAR(13) + CHAR(10)
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;

SELECT @SqlStatement += 
    COALESCE(@SqlStatement, '') + 'DROP TABLE ' + @schema +'.'+ QUOTENAME(TABLE_NAME) + ';'  + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @schema

EXECUTE sp_executesql @SqlStatement

Just in case it helps someone, I added this as a stored procedure to the master database to allow it to conveniently used on any db / schema.

It can be called like this:

EXEC master.dbo.dropTablesInSchema 'my_db', 'dbo

Stored procedure create script:

CREATE PROC [master].[dbo].[dropTablesInSchema]
	@db nvarchar(max),
	@schema nvarchar(max)
AS
BEGIN
	DECLARE @Tables TABLE (name nvarchar(max))
	INSERT INTO @Tables
	EXEC ('SELECT TABLE_NAME FROM [' + @db + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''' + @schema + ''' and TABLE_TYPE =''BASE TABLE''')

	DECLARE @SqlStatement NVARCHAR(MAX)
	SELECT @SqlStatement = 
		COALESCE(@SqlStatement, N'') + N'DROP TABLE [' + @db + '].[' + @schema + '].' + QUOTENAME(NAME) + N';' + CHAR(13)
	FROM @Tables
	
	EXEC(@SqlStatement)

END

Building on chris LB's answer, I added

GROUP BY d.constraint_object_id, t.name

because I saw duplicate constraint deletions in my query. constraint_object_id is the FK Constraint ID, as noted at https://msdn.microsoft.com/en-us/library/ms186306.aspx

DECLARE @SqlStatement NVARCHAR(MAX),
		@Schema NVARCHAR(20)

SET @Schema = 'aa'

SELECT @SqlStatement = 
	COALESCE(@SqlStatement,'') + 'ALTER TABLE '+@Schema+'.' + t.name + ' DROP CONSTRAINT ' + 
	OBJECT_NAME(d.constraint_object_id)  + ';' + CHAR(13) + CHAR(10)
FROM sys.tables t
    JOIN sys.foreign_key_columns d on t.object_id = d.parent_object_id 
    INNER JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE s.name = @Schema
GROUP BY d.constraint_object_id, t.name
ORDER BY t.name;

select 'DROP TABLE [TABSCHEMA].' + QUOTENAME(TABLE_NAME) + N';' from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TABSCHEMA' and TABLE_TYPE = 'BASE TABLE'

This will generate all the DROP TABLE and DROP VIEW with check exists.

DECLARE @SqlStatement NVARCHAR(MAX)

SELECT @SqlStatement = 
COALESCE(@SqlStatement, N'') + N'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+'['+TABLE_SCHEMA+'].' + QUOTENAME(TABLE_NAME) +''' )' + CHAR(13)+
 '	DROP '+ TABLE_TYPE +' ['+TABLE_SCHEMA+'].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA in ('SCHEMA1','SCHEMA2','SCHEMA13' )
ORDER BY TABLE_SCHEMA   

PRINT  REPLACE(@SqlStatement,'DROP BASE TABLE ','DROP TABLE ') 
GO

Drop all tables in schema , can be modified to return any subset of tables.

declare @schema varchar(10) = 'temp' 
declare @max_number_of_tables int = 1000
declare @sql nvarchar(max)
declare @index int = 0


while (
select count(*)
from
	sys.objects obj
	join sys.schemas s
		on (s.schema_id=obj.schema_id)
where
	s.name= @schema 
	and obj.type = 'U'
	AND obj.is_ms_shipped = 0x0) > 0 and @index < @max_number_of_tables
begin
  set @index = @index+1

  select top 1
	@sql = N'DROP TABLE [' + @schema + '].[' + obj.name + ']'
  from
	sys.objects obj
	join sys.schemas s
		on (s.schema_id=obj.schema_id)
  where
	s.name = @schema
	and obj.type = 'U'
	AND obj.is_ms_shipped = 0x0
  order by obj.name

  print @sql

  execute(@sql)
end

Modification of acepted answer that works just copy pasted.

Change db to your database and set @dbSchema to your schema.

USE db -- CHANGE TO YOUR DB
GO

DECLARE @dbSchema NVARCHAR(200);
SET @dbSchema = 'dbo'  -- CHANGE TO YOUR SCHEMA

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

EXEC sp_executesql @SqlStatement