SQL Server 데이터베이스의 모든 테이블을 삭제하는 방법

programing

SQL Server 데이터베이스의 모든 테이블을 삭제하는 방법

instargram 2023. 4. 7. 20:54

SQL Server 데이터베이스의 모든 테이블을 삭제하는 방법

SQL Server 데이터베이스를 완전히 비우는 스크립트를 작성하려고 합니다.지금까지 제가 알아낸 건 다음과 같습니다.

USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'

Management Studio에서 실행하면 다음과 같은 메시지가 나타납니다.

명령이 성공적으로 완료되었습니다.

테이블 리스트를 갱신하면, 모두 그대로입니다.내가 뭘 잘못하고 있지?

(SQL 스크립트를 사용하지 않고) MSSMS UI 도구만 사용하여 데이터베이스에서 모든 테이블을 삭제할 수도 있습니다.때로는 이 방법이 더 편안할 수 있습니다(특히 가끔 실행하는 경우).

이 작업은 다음과 같이 단계적으로 실시합니다.

데이터베이스 트리에서 '테이블'을 선택합니다(개체 탐색기).
F7 키를 눌러 개체 탐색기 세부 정보 보기를 엽니다.
이 뷰에서는 삭제할 테이블을 선택합니다(이 경우 모두).
모든 테이블이 삭제될 때까지 [Delete]를 계속 누릅니다(키 제약/의존관계로 인한 오류 수만큼 반복).

외국 키 테이블이 여러 개 있을 때도 안 돼요.
모든 작업을 수행하는 코드를 찾았습니다(데이터베이스에서 모든 테이블 삭제).

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  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

게시물은 여기서 찾을 수 있습니다.Groker의 투고입니다.

SSMS의 경우:

데이터베이스를 오른쪽 클릭합니다.
'태스크'로 이동
[스크립트 생성]을 클릭합니다.
"개체 선택" 섹션에서 "전체 데이터베이스 및 모든 데이터베이스 개체 스크립팅"을 선택합니다.
[스크립팅 옵션 설정]섹션에서 [상세설정]버튼을 클릭합니다.
Script DROP and CREATE에서 Script CREATE를 Script DROP으로 전환하고 OK를 누릅니다.
그런 다음 파일, 클립보드 또는 새 쿼리 창에 저장합니다.
스크립트를 실행합니다.

그러면 데이터베이스를 포함한 모든 것이 삭제됩니다.드롭하지 않을 아이템의 코드를 반드시 삭제해 주세요.또는 "개체 선택" 섹션에서 전체 데이터베이스를 스크립팅하도록 선택하는 대신 제거할 항목을 선택하십시오.

승인된 답변은 Azure를 지원하지 않습니다.문서화되어 있지 않은 저장 프로시저 "sp_MSforeachable"을 사용합니다.실행 중 "azure could not find stored procedure 'sp_msforeachable" 오류가 발생하거나 문서화되어 있지 않은 기능(언제든지 삭제하거나 기능을 변경할 수 있음)에 의존하지 않도록 하려면 다음 절차를 수행합니다.

이 버전은 엔티티 프레임워크 마이그레이션 기록 테이블 "_Migration"을 무시합니다.History" 및 Azure 테이블인 "database_firewall_rules"는 삭제할 권한이 없습니다.

Azure에서 가볍게 테스트.이것이 환경에 바람직하지 않은 영향을 주지 않도록 체크해 주세요.

DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    EXEC(@sql)
    PRINT @sql
END

WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
    EXEC(@sql)
    PRINT @sql
END

취득처:

https://edspencer.me.uk/2013/02/25/drop-all-tables-in-a-sql-server-database-azure-friendly/

http://www.sqlservercentral.com/blogs/sqlservertips/2011/10/11/remove-all-foreign-keys/

delete는 테이블에서 행을 삭제하는 데 사용됩니다. 하면 됩니다.drop table★★★★★★ 。

EXEC sp_msforeachtable 'drop table [?]'

거의 맞습니다. 대신 다음을 사용하십시오.

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DROP TABLE ?'

그러나 두 번째 행은 오류가 더 이상 나타나지 않을 때까지 여러 번 실행해야 할 수 있습니다.

Could not drop object 'dbo.table' because it is referenced by a FOREIGN KEY constraint.

메시지:

Command(s) completed successfully.

모든 테이블이 정상적으로 삭제되었음을 나타냅니다.

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

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

짧고 달콤함:

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

딱 들어맞아!!

아래 쿼리를 사용하여 데이터베이스에서 모든 테이블을 제거할 수 있습니다.

EXEC sp_MSforeachable @command1 = "DROP TABLE?"

해피 코딩!

네모난 담요를 쓰지 말고

EXEC sp_msforeachtable 'drop table ?'

빠른 방법은 다음과 같습니다.

새 데이터베이스 다이어그램
모든 테이블 추가
Ctrl + A를 눌러 모두 선택
"데이터베이스에서 삭제" 오른쪽 클릭
저장할 Ctrl + S
즐거운 시간 되세요.

저에게 가장 쉬운 방법은 다음과 같습니다.

--First delete all constraints

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
ALTER TABLE ' + QUOTENAME(s.name) + N'.'
+ QUOTENAME(t.name) + N' DROP CONSTRAINT '
+ QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s 
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

EXEC sys.sp_executesql @sql;

-- Then drop all tables

exec sp_MSforeachtable 'DROP TABLE ?'

dbo + ipv6_database_firewall_rules 조건과는 다른 스키마의 Azure SQL + 테이블(제한 포함)

이것은 https://stackoverflow.com/a/43128914/4510954의 답변에 대한 약간의 확장입니다.

DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    SELECT TOP 1 @sql=('ALTER TABLE ' + CONSTRAINT_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    EXEC(@sql)
    PRINT @sql
END

WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules' AND TABLE_NAME != 'ipv6_database_firewall_rules'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + CONSTRAINT_SCHEMA  + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
    EXEC(@sql)
    PRINT @sql
END

전체 데이터베이스를 삭제한 후 다시 작성하는 것은 어떻습니까?난 이거면 돼.

DROP DATABASE mydb;
CREATE DATABASE mydb;

Temporal Table의 경우 외부 키와 예외가 있을 수 있기 때문에 조금 더 복잡합니다.

Drop table operation failed on table XXX because it is not a supported operation on system-versioned temporal tables

사용할 수 있는 것은 다음과 같습니다.

-- Disable constraints (foreign keys)
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- Disable system versioning (temporial tables)
EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableTemporalType'') = 2
  ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)
'
GO

-- Removing tables
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

sp_msforeachtable SQL에서할 수 .

Azure SQL의 경우:

이 쿼리는 외부 키 제약 조건을 삭제합니다.

DECLARE @Name VARCHAR(200)
DECLARE @Constraint VARCHAR(300)
DECLARE @SQL VARCHAR(300)

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

그러면 데이터베이스에서 모든 테이블이 삭제됩니다.

DECLARE @Name VARCHAR(200)
DECLARE @SQL VARCHAR(300)

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) +']' /*here you can change schema if it is different from dbo*/
    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

오래된 포스트인 것은 알지만, 많은 데이터베이스에서 이 모든 답변을 시도해 보았습니다.또한 SQL Server의 다양한 (전혀 짐작할 수 없는) 기호에 따라서는 모두 동작하는 경우도 있습니다.

결국 나는 이것을 생각해냈다.할 수 있는 모든 곳에서 테스트해 보았습니다(일반적으로 말하면), (숨겨진 점포의 절차 없이) 동작합니다.

주로 SQL Server 2014에 관한 것입니다.(다른 버전도 대부분 정상적으로 동작하고 있는 것 같습니다.)

루프나 늘 등, 커서 등 다양한 형태로 시도했지만, 일부 데이터베이스에서는 항상 장애가 발생하고 다른 데이터베이스에서는 뚜렷한 이유 없이 장애가 발생하지 않는 것 같습니다.

숫자를 세고 그것을 반복하는데 사용하는 것은 항상 내가 테스트한 모든 것에 효과가 있는 것처럼 보인다.

USE [****YOUR_DATABASE****]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Drop all referential integrity constraints --
-- Drop all Primary Key constraints.          --

DECLARE @sql  NVARCHAR(296)
DECLARE @table_name VARCHAR(128)

DECLARE @constraint_name VARCHAR(128)
SET @constraint_name = ''

DECLARE @row_number INT

SELECT @row_number = Count(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME

WHILE @row_number > 0
BEGIN
    BEGIN
        SELECT TOP 1 @table_name = tc2.TABLE_NAME, @constraint_name = rc1.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
        LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME
        AND rc1.CONSTRAINT_NAME > @constraint_name
        ORDER BY rc1.CONSTRAINT_NAME
        SELECT @sql = 'ALTER TABLE [dbo].[' + RTRIM(@table_name) +'] DROP CONSTRAINT [' + RTRIM(@constraint_name)+']'
        EXEC (@sql)
        PRINT 'Dropped Constraint: ' + @constraint_name + ' on ' + @table_name
        SET @row_number = @row_number - 1
    END
END
GO

-- Drop all tables --

DECLARE @sql  NVARCHAR(156)
DECLARE @name VARCHAR(128)
SET @name = ''

DECLARE @row_number INT

SELECT @row_number = Count(*) FROM sysobjects WHERE [type] = 'U' AND category = 0

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

언급URL : https://stackoverflow.com/questions/8439650/how-to-drop-all-tables-in-a-sql-server-database

'programing' 카테고리의 다른 글

MVVM에서 View Model 또는 Model은 INotify를 구현해야 합니다.속성이 변경되었습니까? (0)	2023.04.12
SQL Server - 여기서 "sys" 입니다.기능?" (0)	2023.04.12
T-SQL 캐스트와 변환 (0)	2023.04.07
대용량 MS SQL .sql 파일을 Import하려면 어떻게 해야 합니까? (0)	2023.04.07
SQL Server에서 외부 키를 드롭하려면 어떻게 해야 합니까? (0)	2023.04.07

현재글SQL Server 데이터베이스의 모든 테이블을 삭제하는 방법

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

MariaDB, MongoDB, mysql, jQuery, Spring-boot, reactjs, Android, wordpress, python, Ajax, Git, angularJS, sql-server, CSS, Oracle, json, Excel, asp.net, typescript, C,

Today :
Yesterday :

instargram

SQL Server 데이터베이스의 모든 테이블을 삭제하는 방법

SQL Server 데이터베이스의 모든 테이블을 삭제하는 방법

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2025/03 »
일	월	화	수	목	금	토
						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	31

SQL Server 데이터베이스의 모든 테이블을 삭제하는 방법

SQL Server 데이터베이스의 모든 테이블을 삭제하는 방법

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바