Как удалить все таблицы в базе данных SQL Server? - программирование
Подтвердить что ты не робот

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

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

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

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

Команда успешно завершена.

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

4b9b3361

ОТВЕТЫ

Ответ 1

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

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

Вы можете найти сообщение здесь. Это сообщение от Грокера.

Ответ 2

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

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

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

Ответ 3

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

EXEC sp_msforeachtable 'drop table [?]'

Ответ 4

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

Ответ 5

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

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.

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

Ответ 6

В SSMS:

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

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

Ответ 7

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

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

Ответ 8

Принятый ответ не поддерживает 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/

Ответ 9

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

EXEC sp_msforeachtable 'drop table ?'

Ответ 10

Пост:

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

Ответ 11

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

DROP DATABASE mydb;
CREATE DATABASE mydb;

Ответ 12

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

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

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

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

Получение счета и использование этого для итерации всегда, кажется, работают на всех проверенных Ive.

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

Ответ 13

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

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

Ответ 14

Если вы хотите удалить все таблицы, вы можете также удалить базу данных, а затем создать новую. Чтобы добиться этого в SQL Management Studio (в моем случае v 17.9.1):

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

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

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