SQL Server 2008은 특수 스키마 아래의 모든 테이블을 삭제합니다.

SQL Server 2008은 특수 스키마 아래의 모든 테이블을 삭제합니다.

---

안녕하세요, DBO1과 같은 사용자 정의 스키마에서 생성 된 데이터베이스의 모든 테이블을 하나의 SQL 쿼리 또는 특수 스크립트로 삭제할 수 있다는 것을 알고 싶습니다.

감사

---

이것은 모든 DROP TABLE 문을 생성하고 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

---

내가 아는 다소 오래된 스레드이지만 이와 같은 것을 찾고 있었고 원래 답변이 매우 유용하다는 것을 알았습니다. 즉, 스크립트는 해당 스키마에있을 수있는 뷰를 삭제하려고 시도하고 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

---

@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;

---

다른 답변을 바탕으로 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

---

이것이 오래된 스레드라는 것을 알고 있지만 가장 쉬운 방법은 문서화되지 않은 sp_MSforeachtable저장 프로 시저를 사용하는 것입니다 .

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

이 저장 프로 시저에 대한 자세한 보고서는 여기 에서 찾을 수 있지만 링크가 끊어진 경우 여기에서 주요 내용이 있습니다.

sp_MSforeachtable은 현재 데이터베이스에있는 모든 테이블에 T-SQL 명령을 반복적으로 적용하는 데 주로 사용되는 저장 프로 시저입니다.
[...]
물음표 (?)가 테이블의 대체로 사용되고 실행 중에 적절한 테이블 이름으로 대체된다는 것을 깨달았습니다.

@ command1, @ command2, @ command3
sp_MSforeachtable 저장 프로시 저는 하나 이상의 명령을 실행해야하지만 (@ command1) 최대 3 개의 명령을 실행할 수 있습니다. 먼저 @ command1을 실행 한 다음 마지막으로 @ command2 및 @ command3을 실행하고 각 테이블에 대해 이것을 실행합니다.

@precommand
이 매개 변수를 사용하여 @ command1 전에 실행할 명령을 제공합니다. 변수 환경을 설정하거나 모든 종류의 초기화를 수행하는 것이 유용합니다.

@postcommand
모든 명령이 성공적으로 실행 된 후 실행할 명령을 제공하려면이 매개 변수를 사용합니다. 제어 및 정리 프로세스에 유용합니다.

@replacechar
기본적으로 테이블은 물음표 (?) 문자로 표시됩니다. 이 매개 변수를 사용하면이 문자를 변경할 수 있습니다.

@whereand
기본적으로 sp_MSforeachtable은 데이터베이스의 모든 사용자 테이블에 적용됩니다. 이 매개 변수를 사용하여 작업 할 테이블을 필터링하십시오. 다음 섹션에서는 테이블을 필터링하는 방법에 대해 설명합니다.

---

또한 @Kevo의 답변을 기반으로 TSQL Print 문에서 발생한 문제에 대해 다음 while 루프를 추가했습니다. 메시지 문자열은 최대 8,000 자까지 가능합니다. 8,000보다 크면 print 문은 나머지 문자를 모두 자릅니다.

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

---

@ raider33과 @Kevo의 답변을 하나의 솔루션으로 결합하여 직접 실행했습니다.

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

---

chris LB의 답변을 바탕으로 추가했습니다.

GROUP BY d.constraint_object_id, t.name

내 쿼리에서 중복 제약 조건 삭제를 보았 기 때문입니다. https://msdn.microsoft.com/en-us/library/ms186306.aspx에constraint_object_id 명시된 FK 제약 조건 ID입니다.

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;

---

누군가에게 도움이되는 경우를 대비하여 모든 db / schema에서 편리하게 사용할 수 있도록 master 데이터베이스에 저장 프로 시저로 추가했습니다.

다음과 같이 호출 할 수 있습니다.

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

저장 프로 시저 작성 스크립트 :

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

---

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

---

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

---

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

ReferenceURL : https://stackoverflow.com/questions/8933976/sql-server-2008-delete-all-tables-under-special-schema