SQL Server 2008 특수 스키마에서 모든 테이블 삭제

programing

SQL Server 2008 특수 스키마에서 모든 테이블 삭제

itmemos 2023. 7. 6. 21:50

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

, 은 문서화되지 ▁the▁but▁i▁using▁undo다▁by생합니각▁the▁thinkmented▁to▁is▁icu▁is오▁way▁this▁easiest▁an▁do▁thread▁old고▁this▁know래라방가를 사용하는 것이라고 생각합니다.sp_MSforeachtable저장 프로시저:

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

이 저장 프로시저에 대한 자세한 보고서는 여기에서 확인할 수 있지만 링크가 비활성화되는 경우 다음과 같은 주요 내용이 있습니다.

각 테이블의 sp_MS는 T-SQL 명령을 현재 데이터베이스에 있는 모든 테이블에 반복적으로 적용하는 데 주로 사용되는 저장 프로시저입니다.
[...]
하는 데 표 을 깨달았습니다.

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

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

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

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

@어디서나
기본적으로 각 테이블의 sp_MS는 데이터베이스의 모든 사용자 테이블에 적용됩니다.이 매개 변수를 사용하여 작업할 테이블을 필터링합니다.다음 섹션에서는 테이블을 필터링하는 방법을 설명하겠습니다.

다른 답변을 바탕으로 저장 프로시저가 있습니다.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

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

@Kevo의 답변을 바탕으로 테이블을 삭제하기 전에 모든 외부 키 제약 조건을 삭제하기 위해 다음을 추가했습니다.SQL 2008 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;

또한 @Kevo의 답변을 바탕으로 TSQL Print 문의 문제를 루프하면서 다음과 같이 추가했습니다.메시지 문자열의 길이는 최대 8,000자입니다.8,000자를 초과하면 인쇄 문이 나머지 문자를 잘라냅니다.

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

누군가에게 도움이 될까봐 마스터 데이터베이스에 저장 프로시저로 추가하여 어떤 db/schema에서도 편리하게 사용할 수 있도록 하였습니다.

다음과 같이 부를 수 있습니다.

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

크리스 LB의 대답을 바탕으로, 나는 덧붙였습니다.

GROUP BY d.constraint_object_id, t.name

쿼리에서 중복된 제약 조건 삭제를 보았기 때문입니다. constraint_object_id는 FK 제약 조건 ID입니다(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'

그러면 모든 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

스키마의 모든 테이블을 삭제합니다. 테이블의 하위 집합을 반환하도록 수정할 수 있습니다.

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

복사 붙여넣기만 하면 작동하는 승인된 답변의 수정.

바꾸다db데이터베이스 및 세트에@dbSchema스키마로 이동합니다.

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

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

'programing' 카테고리의 다른 글

.NET의 디버그 대 릴리스 (0)	2023.07.06
JdbcTemplate "queryForObject" 및 "query"는 봄에 더 이상 사용되지 않습니다.무엇으로 대체해야 합니까? (0)	2023.07.06
SQL 조회를 사용하여 테이블 존재 여부 확인 (0)	2023.07.01
CS0433 "A.dll과 B.dll 모두에 유형 'X'가 이미 존재합니다"라는 오류는 어디에서 발생합니까? (0)	2023.07.01
Imports/Depends 사용 시기에 대한 더 나은 설명 (0)	2023.07.01

현재글SQL Server 2008 특수 스키마에서 모든 테이블 삭제

각종 프로그래밍 정보를 다루는 블로그입니다.

Excel, Ajax, CSS, Android, C, oracle, git, MySQL, mariadb, Wordpress, MongoDB, jQuery, Python, Reactjs, sql-server, ASP.NET, json, PowerShell, spring-boot, angularJs,

Today :
Yesterday :

일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

itmemos

SQL Server 2008 특수 스키마에서 모든 테이블 삭제

SQL Server 2008 특수 스키마에서 모든 테이블 삭제

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

SQL Server 2008 특수 스키마에서 모든 테이블 삭제

SQL Server 2008 특수 스키마에서 모든 테이블 삭제

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바