SQL Server 2008 удаляет все таблицы в специальной схеме

Ответ 1

Это сгенерирует все инструкции DROP TABLE для вас и PRINT вывод SQL. Затем вы можете проверить его, что вы ожидаете, перед копированием и исполнением. Просто убедитесь, что вы на 100% уверены... возможно, сначала сделайте резервную копию:)

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

Ответ 2

Немного старый поток, который я знаю, но я искал что-то вроде этого и нашел оригинальный ответ очень полезным. Тем не менее, script также попытается отбросить представления, которые могут существовать в этой схеме, и дать вам сообщение об ошибке, потому что вы в конечном итоге пытаетесь удалить представление, выпустив инструкцию DROP TABLE.

В итоге я написал это, потому что мне нужно было отбросить все таблицы, представления, процедуры и функции из данной схемы. Возможно, это не самый элегантный способ добиться этого, но он работал у меня, и я думал, что поделюсь.

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

Ответ 3

Основываясь на ответе @Kevo, я добавил следующее для удаления всех ограничений внешнего ключа перед удалением таблиц. Я тестировал только 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;

Ответ 4

Основываясь на других ответах, здесь хранится процедура spDropSchema, которая удаляет все объекты в схеме и самой схеме.

Обратите внимание, что процедура также пытается удалить объекты последовательности, поэтому она будет работать только на SQL Server 2012 и выше.

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

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

EXECUTE sp_executesql @Sql

GO

Ответ 5

Также, основываясь на ответе @Kevo, я добавил следующий цикл while для проблемы, с которой я столкнулся с выражением TSQL Print. Строка сообщения может содержать до 8 000 символов. Если более 8000, оператор печати будет усекать любые оставшиеся символы.

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

Ответ 6

Я объединил ответы от @raider33 и @Kevo на одно решение для прямого выполнения.

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

select @SqlStatement = COALESCE(@SqlStatement,'') + 'ALTER TABLE '[email protected]+'.' + 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

Ответ 7

Основываясь на chris LB answer, я добавил

GROUP BY d.constraint_object_id, t.name

потому что в моем запросе я видел дубликаты ограничений. constraint_object_id - это идентификатор ограничения FK, как указано в 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 '[email protected]+'.' + 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;

Ответ 8

На всякий случай, это помогает кому-то, я добавил это как хранимую процедуру в основную базу данных, чтобы позволить ей удобно использовать любую db/схему.

Его можно вызвать так:

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

Сохраненная процедура создает 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

Ответ 9

Это приведет к созданию всех DROP TABLE и DROP VIEW с проверкой наличия.

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