Как удалить все таблицы в базе данных SQL Server?

Question

Как удалить все таблицы в базе данных SQL Server?

Я пытаюсь написать скрипт, который полностью очистит базу данных SQL Server. Это то, что я до сих пор:

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

Когда я запускаю его в Management Studio, я получаю:

Команда (и) выполнена успешно.

но когда я обновляю список таблиц, они все еще там. Что я делаю неправильно?

284

sql-server sql-server-2008 ssms sp-msforeachtable

Источник

user1088820 09 дек '11 в 00:30

18 ответов

Решение

Вы также можете удалить все таблицы из базы данных, используя только инструменты MSSMS (без использования sql). Иногда этот способ может быть более удобным (особенно если он выполняется время от времени)

Я делаю это шаг за шагом следующим образом:

Выберите "Таблицы" в дереве базы данных (Object Explorer)
Нажмите клавишу F7, чтобы открыть представление "Подробности обозревателя объектов".
В этом представлении выберите таблицы, которые необходимо удалить (в данном случае все)
Нажимайте "Удалить", пока все таблицы не будут удалены (вы повторяете это столько раз, сколько ошибок из-за ключевых ограничений / зависимостей)

511

Источник

user769465 25 апр '14 в 17:09

В SSMS:

Щелкните правой кнопкой мыши базу данных
Перейти к "Задачам"
Нажмите "Создать сценарии"
В разделе "Выбор объектов" выберите "Сценарий всей базы данных и всех объектов базы данных".
В разделе "Настройка параметров сценариев" нажмите кнопку "Дополнительно"
На "Script DROP and CREATE" переключите "Script CREATE" на "Script DROP" и нажмите ОК
Затем либо сохранить в файл, буфер обмена или новое окно запроса.
Запустить скрипт.

Теперь это отбросит все, включая базу данных. Убедитесь, что удалили код для предметов, которые вы не хотите отбрасывать. Кроме того, в разделе "Выбор объектов" вместо выбора сценария для всей базы данных просто выберите элементы, которые вы хотите удалить.

112

Источник

user6105961 17 апр '18 в 20:15

Принятый ответ не поддерживает Azure. Он использует недокументированную хранимую процедуру "sp_MSforeachtable". Если вы получаете сообщение об ошибке "Azure не удалось найти хранимую процедуру" sp_msforeachtable "при запуске или просто хотите избежать использования недокументированных функций (которые можно удалить или изменить их функциональность в любой момент)", попробуйте следующее.

В этой версии игнорируются таблица истории миграции структуры сущностей "__MigrationHistory" и таблица "database_firewall_rules", которая является таблицей Azure, которую вы не имеете права удалять.

Слегка проверено на 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/

74

Источник

user74178 30 мар '17 в 21:44

delete используется для удаления строк из таблицы. Вы должны использовать drop table вместо.

EXEC sp_msforeachtable 'drop table [?]'

51

Источник

user383710 09 дек '11 в 00:35

Вы почти правы, используйте вместо этого:

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.

означает, что все таблицы были успешно удалены.

36

Источник

user1027198 08 янв '14 в 11:36

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

31

Источник

user548585 21 фев '13 в 08:49

Коротко и сладко:

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

23

Источник

user1792831 16 сен '16 в 02:58

Путь поста:

Новые диаграммы базы данных
Добавить всю таблицу
Ctrl + A, чтобы выбрать все
Щелкните правой кнопкой мыши "Удалить из базы данных"
Ctrl + S, чтобы сохранить
наслаждаться

11

Источник

user4272770 24 фев '17 в 08:18

Пятно!!

Вы можете использовать запрос ниже, чтобы удалить все таблицы из базы данных

EXEC sp_MSforeachtable @command1 = "УДАЛИТЬ ТАБЛИЦУ?"

Удачного кодирования!

11

Источник

user7873483 02 мар '20 в 13:12

Кажется, команда должна быть без квадратного одеяла

EXEC sp_msforeachtable 'drop table ?'

10

Источник

user3958517 22 июн '16 в 06:57

Для меня самый простой способ:

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

7

Источник

user9210118 12 янв '18 в 17:08

Как насчет удаления всей базы данных и последующего ее создания? Это работает для меня.

DROP DATABASE mydb;
CREATE DATABASE mydb;

4

Источник

user2691226 04 авг '15 в 09:50

Таблицы Azure SQL + (с ограничениями) в схеме, отличной от схемы dbo + ipv6_database_firewall_rules.

Это небольшое расширение для ответа /questions/15564074/kak-udalit-vse-tablitsyi-v-baze-dannyih-sql-server/15564081#15564081

      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

4

Источник

user4510954 13 янв '22 в 11:00

sp_msforeachtableнедоступен в Azure 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

3

Источник

user13086128 31 окт '22 в 09:06

Для временных таблиц это немного сложнее из-за того, что могут быть некоторые внешние ключи, а также исключение:

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

2

Источник

user6330442 14 фев '20 в 14:42

Я знаю, что это старый пост, но я попробовал все ответы здесь на множестве баз данных, и я обнаружил, что все они иногда работают, но не все время для различных (я могу только предположить) причуд SQL Server.

В конце концов я придумал это. Я проверял это везде (вообще говоря), я могу, и это работает (без каких-либо скрытых процедур магазина).

Обратите внимание, в основном на SQL Server 2014. (но большинство других версий, которые я пробовал, также, кажется, работало нормально).

Я пробовал циклы while и null и т. Д., Т. Д., Курсоры и другие формы, но в некоторых базах данных они, похоже, всегда выходили из строя, а в других - без видимой причины.

Получение счетчика и его использование для повторения всегда, кажется, работает на всем, что я проверял.

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

1

Источник

user541107 24 дек '17 в 01:05

Если вы хотите удалить все таблицы, вы можете также удалить базу данных, а затем создать новую.

T-SQL:

DROP DATABASE Northwind
ИДТИ
СОЗДАТЬ БАЗУ ДАННЫХ Northwind
ИДТИ

Чтобы добиться этого в SQL Management Studio (SSMS, в моем случае v 17.9.1):

Щелкните правой кнопкой мыши по базе данных, которую вы хотите удалить, например, "Northwind";
Выберите "Удалить" в контекстном меню
Щелкните правой кнопкой мыши на "Базы данных" (значок папки);
Выберите "Новая база данных..."
Введите имя базы данных, например "Northwind";
Нажмите ОК

И вы сделали! У вас есть свежая база данных без таблиц. Преимущество этого подхода заключается в том, что вам не нужно заботиться о каких-либо ограничениях, запрещающих удалять таблицы, зависимые представления или SP, которые больше не работают, а также вам не нужны динамические сценарии SQL. Вы можете добавить их позже, если вы сделали резервную копию, так

Не забудьте сделать резервную копию вашей базы данных, прежде чем сделать это! Если вам просто нужны некоторые SP или представления, сохраните их как скрипт, прежде чем заново создавать базу данных, отредактируйте их, чтобы они работали с новыми таблицами, и добавьте их позже.

0

Источник

user1016343 04 фев '19 в 16:19

Другие вопросы по тегам sql-server sql-server-2008 ssms sp-msforeachtable

user888479 09 дек '11 в 00:53 2011-12-09 00:53 · Accepted Answer · 2011-12-09 00:53

Это также не работает для меня, когда есть несколько таблиц внешнего ключа.
Я нашел этот код, который работает и делает все, что вы пытаетесь (удалить все таблицы из вашей базы данных):

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

Вы можете найти пост здесь. Это сообщение от Groker.