programing

SQL 쿼리가 1개인 데이터베이스에서 모든 테이블을 삭제하려면 어떻게 해야 합니다.

elecom 2023. 4. 8. 07:59
반응형

SQL 쿼리가 1개인 데이터베이스에서 모든 테이블을 삭제하려면 어떻게 해야 합니다.

모든 테이블의 이름을 입력해서 모든 테이블을 삭제하고 싶지는 않습니다.하나의 질문으로 가능한가요?

INFORMATION_SCHEMA를 사용합니다.테이블 목록을 가져오는 테이블 뷰.select 스테이트먼트에서 드롭 스크립트를 생성하고 Dynamic SQL을 사용하여 드롭합니다.

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

시스템 테이블 버전

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

Exec sp_executesql @sql

주의: 있는 경우foreign Keys테이블 간에 정의된 후 먼저 아래 쿼리를 실행하여 모든 것을 비활성화합니다.foreign keys데이터베이스에 있습니다.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

자세한 내용은 여기를 참조하십시오.

SQL 쿼리를 하나만 사용하여 모든 테이블을 삭제하는 경우 다음을 사용할 수 있습니다.

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

이것은 SQL 서버의 숨겨진 저장 프로시저이며 연결된 데이터베이스의 각 테이블에 대해 실행됩니다.

주의: 의존관계로 인해 모든 테이블을 삭제하려면 쿼리를 여러 번 실행해야 할 수 있습니다.

참고 2: 첫 번째 노트를 피하려면 쿼리를 실행하기 전에 먼저 외부 키가 테이블에 연결되어 있는지 확인하십시오.아래 쿼리를 실행하여 외부 키 제약 조건을 비활성화하는 경우:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

입력하지 않는 경우는, 다음과 같이 문장을 작성할 수 있습니다.

USE Databasename

SELECT  'DROP TABLE [' + name + '];'
FROM    sys.tables

그런 다음 새 SSMS 창에 복사하여 붙여넣기하여 실행합니다.

가장 간단한 방법은 데이터베이스 전체를 삭제하고 다시 작성하는 것입니다.

drop database db_name
create database db_name

그게 다예요.

또한 다음 스크립트를 사용하여 다음을 포함한 모든 항목을 삭제할 수도 있습니다.

  • 비시스템 스토어드 프로시저
  • 뷰
  • 기능들
  • 외부 키 제약
  • 주요 제약 사항
  • system_versioning
  • 테이블

https://michaelreichenbach.de/posts/how-to-drop-everything-in-a-mssql-database/

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Remove system versioning */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    IF OBJECTPROPERTY(OBJECT_ID(' + @name + '), 'TableTemporalType') = 2 
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] SET (SYSTEM_VERSIONING = OFF); ALTER TABLE [dbo].[' + RTRIM(@name) + '] DROP PERIOD FOR SYSTEM_TIME;'
        EXEC (@SQL)
        PRINT 'System Versioning Disabled for Table: ' + @name
        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

데이브의 후속 조치로서요Gugg의 답변은 MySQL의 모든 DROP TABLE 행을 얻기 위해 필요한 코드입니다.

SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA = 'your_database_name'

@NoDisplayName의 답변과 사용법을 약간 변경합니다.QUOTENAME()에서TABLE_NAME컬럼과 함께TABLE_SCHEMA테이블이 에 없습니다.dbo스키마

DECLARE @sql nvarchar(max) = '';

SELECT @sql += 'DROP TABLE ' + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME]) + ';'
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [TABLE_TYPE] = 'BASE TABLE';

EXEC SP_EXECUTESQL @sql;

또는 사용sys스키마 뷰(@swascheck 코멘트):

DECLARE @sql nvarchar(max) = '';

SELECT @sql += 'DROP TABLE ' + QUOTENAME([S].[name]) + '.' + QUOTENAME([T].[name]) + ';'
FROM [sys].[tables] AS [T]
INNER JOIN [sys].[schemas] AS [S] ON ([T].[schema_id] = [S].[schema_id])
WHERE [T].[type] = 'U' AND [T].[is_ms_shipped] = 0;

EXEC SP_EXECUTESQL @sql;

베스트 앤서 솔루션(외부 키 비활성화 포함)에 문제가 있는 다른 사용자가 있는 경우, 다음은 저의 다른 해결책입니다.

-- CLEAN DB
USE [DB_NAME]
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSForEachTable 'DELETE FROM ?'

    DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
    SET @Cursor = CURSOR FAST_FORWARD FOR

    SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
    OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
    WHILE (@@FETCH_STATUS = 0)
      BEGIN
        Exec SP_EXECUTESQL @Sql
        FETCH NEXT 
        FROM @Cursor INTO @Sql
      END
    CLOSE @Cursor DEALLOCATE @Cursor
    GO

    EXEC sp_MSForEachTable 'DROP TABLE ?'
    GO

    EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

1개의 쿼리는 아니지만, 여전히 짧고 달콤한 질문입니다.

-- Disable all referential integrity constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Drop all PKs and FKs
declare @sql nvarchar(max)
SELECT @sql = STUFF((SELECT '; ' + 'ALTER TABLE ' + Table_Name  +'  drop constraint ' + Constraint_Name  from Information_Schema.CONSTRAINT_TABLE_USAGE ORDER BY Constraint_Name FOR XML PATH('')),1,1,'')
EXECUTE (@sql)
GO

-- Drop all tables
EXEC sp_msforeachtable 'DROP TABLE ?'
GO

다음 스크립트를 사용하여drop모든.constraints:

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) +    ' NOCHECK CONSTRAINT all; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

그런 다음 다음을 수행하여 모든 테이블을 삭제합니다.

select @sql='';

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

이것은 Azure SQL Database에서 저에게 도움이 되었습니다.'sp_msforeachtable'사용할 수 없었습니다!

이거 드셔보세요

DROP DATABASE database_name;
CREATE DATABASE database_name;

그러면 데이터베이스가 삭제되고 동일한 이름의 새 데이터베이스가 생성됩니다.

왜 이 스크립트를?모든 and Functions and PROCEDURE도 삭제해야 하기 에 Functions and PROCEDURE는 Functions and PROCEDURE를 삭제해야 합니다.
.

DECLARE @sql NVARCHAR(max)=''

 SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) 
+ '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql


 DECLARE @sql VARCHAR(MAX) = ''
    , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

 SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + 
 QUOTENAME(v.name) +';' + @crlf
 FROM   sys.views v

 PRINT @sql;
 EXEC(@sql);

 declare @procName varchar(500)
 declare cur cursor 

 for select [name] from sys.objects where type = 'p'
 open cur
 fetch next from cur into @procName
 while @@fetch_status = 0
 begin
  exec('drop procedure [' + @procName + ']')
fetch next from cur into @procName
 end
  close cur
  deallocate cur

  Declare @sql NVARCHAR(MAX) = N'';

    SELECT @sql = @sql + N' DROP FUNCTION ' 
               + QUOTENAME(SCHEMA_NAME(schema_id)) 
               + N'.' + QUOTENAME(name)
    FROM sys.objects
  WHERE type_desc LIKE '%FUNCTION%';

   Exec sp_executesql @sql
  GO

oracle이나 sqlite를 사용하는 경우 모든 테이블에 대해 루프를 사용합니다.

FOR i IN (SELECT ut.table_name
              FROM USER_TABLES ut) LOOP
    EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
  END LOOP;

저는 그냥...


DECLARE @cnt INT = 0;

WHILE @cnt < 10 --Change this if all tables are not dropped with one run
BEGIN
SET @cnt = @cnt + 1;
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
END

언급URL : https://stackoverflow.com/questions/27606518/how-to-drop-all-tables-from-a-database-with-one-sql-query

반응형

'programing' 카테고리의 다른 글

SQL Server로 CSV 파일 Import  (0) 2023.04.08
변수로서의 테이블 이름  (0) 2023.04.08
SQL JOIN과 IN의 퍼포먼스 비교  (0) 2023.04.08
SQL Server 로컬 및 글로벌 임시 테이블  (0) 2023.04.08
SQL DATE에서 월 및 년만 가져오기  (0) 2023.04.08

'programing'의 다른글

  • 현재글SQL 쿼리가 1개인 데이터베이스에서 모든 테이블을 삭제하려면 어떻게 해야 합니다.

관련글

댓글

티스토리툴바