DB 안에 있는 모든 테이블 삭제
2021. 8. 10. 09:25
master나 tempdb는 제외하고 생성된 모든 테이블을 삭제
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
출처1 : https://stackoverflow.com/questions/8439650/how-to-drop-all-tables-in-a-sql-server-database
How to drop all tables in a SQL Server database?
I'm trying to write a script that will completely empty a SQL Server database. This is what I have so far: USE [dbname] GO EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' EXEC
stackoverflow.com
참고 : https://www.mssqltips.com/sqlservertip/6798/drop-all-tables-sql-server/
Drop All Tables SQL Server
In this article we cover different ways to drop all tables from a SQL Server database.
www.mssqltips.com
728x90