Cara drop semua tabel dari database dengan SQL query?

Saya don't ingin mengetik semua tabel' nama untuk menjatuhkan semua dari mereka. Apakah mungkin dengan satu query?

Majid
Pertanyaan edit 7 Maret 2017 в 5:00
Pemrograman
sql sql-server
22 Desember 2014 в 4:26
22 tayangan
Mengomentari pertanyaan (5)
Larutan

Gunakan INFORMATION_SCHEMA.TABEL lihat untuk mendapatkan daftar tabel. Menghasilkan Drop script di pilih pernyataan dan drop menggunakan 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

Sys.Tabel Versi

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

Catatan: Jika anda memiliki Kunci asing yang didefinisikan antara tabel maka pertama menjalankan query di bawah ini untuk menonaktifkan semua asing tombol hadir di database anda.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Untuk informasi lebih lanjut, cek di sini.

user247702
Jawaban edit 7 Februari 2019 в 4:06
Komentar (9)

Jika anda ingin menggunakan hanya satu query SQL untuk menghapus semua tabel anda dapat menggunakan ini:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Ini adalah tersembunyi yang Tersimpan Prosedur sql server, dan akan dilaksanakan untuk masing-masing tabel dalam database anda'kembali terhubung.

Catatan: Anda mungkin perlu untuk mengeksekusi query beberapa kali untuk menghapus semua tabel karena dependensi.

Note2: Untuk menghindari catatan pertama, sebelum menjalankan query, periksa dulu apakah ada hubungan kunci asing untuk setiap meja. Jika ada maka hanya disable foreign key constraint dengan menjalankan query di bawah ini:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
RageAgainstTheMachine
Jawaban edit 28 Agustus 2019 в 3:16
Komentar (4)

Jika anda don't ingin mengetik, anda dapat membuat pernyataan dengan ini:

USE Databasename

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

Kemudian copy dan paste ke new SSMS jendela untuk menjalankannya.

Dave.Gugg
Jawaban edit 28 Oktober 2016 в 1:12
Komentar (3)

Anda juga bisa menggunakan script berikut untuk menjatuhkan segala sesuatu, termasuk yang berikut:

  • non-sistem prosedur yang tersimpan
  • pandangan
  • fungsi
  • constraint foreign key
  • primary key kendala
  • tabel

https://michaelreichenbach.de/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

/* 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
Silthus
Jawaban edit 13 Maret 2018 в 3:27
Komentar (0)

I'd hanya membuat perubahan kecil untuk @NoDisplayName's jawaban dan menggunakan QUOTENAME() pada TABLE_NAME kolom dan juga termasuk TABLE_SCHEMA kolom menyelimuti tabel aren't dalam dbo skema.

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;

Atau menggunakan sys skema views (sebagai per @swasheck's komentar):

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;
Komentar (6)

Sebagai tindak lanjut Dave.Gugg's jawaban, ini akan menjadi kode seseorang akan perlu untuk mendapatkan semua DROP garis TABEL di MySQL:

SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA = 'your_database_name'
OMA
Jawaban edit 12 November 2015 в 10:12
Komentar (1)

Jika ada orang lain yang punya masalah dengan jawaban terbaik's solusi (termasuk menonaktifkan kunci asing), di sini adalah solusi lain dari saya:

-- 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'
Komentar (0)

Cara paling sederhana adalah dengan menjatuhkan seluruh database dan membuat itu sekali lagi:

drop database db_name
create database db_name

Yang's semua.

Komentar (3)

Gunakan script berikut untuk menjatuhkan semua kendala:

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

Kemudian jalankan perintah berikut untuk drop semua tabel:

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

Ini bekerja untuk saya di Azure SQL Database di mana 'sp_msforeachtable' tidak tersedia!

Chanukya
Jawaban edit 25 Januari 2017 в 5:33
Komentar (0)

Tidak cukup 1 query, masih cukup pendek dan manis:

-- 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
Komentar (0)

Aku Tahu pertanyaan ini sangat lama, tapi setiap Kali aku butuh kode ini .. by the way, jika anda memiliki tabel dan tampilan dan fungsi-Fungsi dan PROSEDUR yang anda dapat menghapus itu semua dengan Script ini .. jadi mengapa saya posting Script ini ?? karena jika anda menghapus semua tabel yang anda akan perlu untuk menghapus semua pandangan dan jika anda memiliki Fungsi dan PROSEDUR yang anda butuhkan untuk menghapusnya juga saya Berharap ini akan membantu seseorang

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
Komentar (0)