Jak wrzucić wszystkie tabele do bazy danych SQL Server?
Próbuję napisać skrypt, który całkowicie opróżni bazę danych SQL Server. To jest to co mam do tej pory:
USE [dbname]
GO
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'
Kiedy uruchamiam go w Studio zarządzania, dostaję:
Polecenie(polecenia) zakończone pomyślnie.
Ale kiedy odświeżę listę tabeli, wszystkie nadal tam są. Co robię źle?
15 answers
To nie działa dla mnie również, gdy istnieje wiele tabel kluczy obcych.
Znalazłem ten kod, który działa i robi wszystko, co próbujesz (usuń wszystkie tabele z bazy danych):
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
Możesz znaleźć post tutaj . To jest post by Groker.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-03-04 20:13:06
Możesz również usunąć wszystkie tabele z bazy danych używając tylko narzędzi MSSMS UI (bez użycia skryptu SQL). Czasami ten sposób może być wygodniejszy (zwłaszcza jeśli jest wykonywany sporadycznie)
Robię to krok po kroku w następujący sposób:
- Wybierz 'tabele' w drzewie bazy danych (Object Explorer)
- naciśnij klawisz F7, aby otworzyć widok szczegółów Eksploratora obiektów
- w tym widoku wybierz tabele, które mają zostać usunięte (w tym przypadku wszystkie)
- wciśnij Delete, aż wszystkie tabele zostały usunięte (powtarzasz je tyle razy, ile błędów spowodowanych kluczowymi ograniczeniami/zależnościami)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-06-12 11:01:37
W SSMS:
- Kliknij prawym przyciskiem myszy bazę danych
- Przejdź do "zadania"
- Kliknij "Generuj Skrypty"
- w sekcji "Wybierz obiekty" wybierz "Script whole database and all database objects"
- w sekcji "Ustaw opcje skryptów" kliknij przycisk "Zaawansowane"
- Na "Script DROP and CREATE "Przełącz" script CREATE "na" Script DROP " i naciśnij OK
- Następnie zapisz do pliku, schowka lub nowego okna zapytania.
- Run scenariusz.
Teraz, to rzuci wszystko, łącznie z bazą danych. Pamiętaj, aby usunąć kod dla przedmiotów, których nie chcesz upuścić. Alternatywnie, w sekcji "Wybierz obiekty", zamiast wybrać skrypt całej bazy danych po prostu wybierz elementy, które chcesz usunąć.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-17 20:15:02
delete
służy do usuwania wierszy z tabeli. Zamiast tego powinieneś użyć drop table
.
EXEC sp_msforeachtable 'drop table [?]'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-23 07:19:10
Zaakceptowana odpowiedź nie obsługuje platformy Azure. Używa nieudokumentowanej procedury składowanej "sp_MSforeachtable". Jeśli podczas uruchamiania pojawi się błąd "azure could not find stored procedure 'sp_msforeachtable" lub po prostu chcesz uniknąć polegania na nieudokumentowanych funkcjach (które można usunąć lub zmienić ich funkcjonalność w dowolnym momencie), spróbuj wykonać poniższe czynności.
Ta wersja ignoruje tabelę historii migracji "__MigrationHistory " i "database_firewall_rules", która jest Usługa Azure table nie będzie miała uprawnień do usunięcia.
Lekko przetestowany na Azure. Sprawdź, aby to zrobić nie ma to niepożądanego wpływu na środowisko.
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
Pobrano z:
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/
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-30 22:07:05
/* 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-02-21 08:49:41
Masz prawie rację, użyj zamiast:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DROP TABLE ?'
Ale druga linia może wymagać wykonania więcej niż jeden raz, dopóki nie przestaniesz otrzymywać błędu:
Could not drop object 'dbo.table' because it is referenced by a FOREIGN KEY constraint.
Wiadomość:
Command(s) completed successfully.
Oznacza, że cała tabela została pomyślnie usunięta.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-01-08 11:36:38
Krótko i słodko:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-22 02:45:45
Wydaje się, że komenda powinna być bez koca kwadratowego
EXEC sp_msforeachtable 'drop table ?'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-06-22 06:57:56
Sposób na czczo to:
- Nowe Diagramy Bazy Danych
- Dodaj całą tabelę
- Ctrl + A aby zaznaczyć wszystkie
- Kliknij prawym przyciskiem myszy "Usuń z bazy danych"
- Ctrl + S aby zapisać
- Enjoy
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-24 08:18:34
Dla mnie najprostszy sposób:
--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 ?'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-12 17:08:55
Spot on!!
Możesz użyć poniższego zapytania, aby usunąć wszystkie tabele z bazy danych
EXEC sp_MSforeachtable @ command1 = " DROP TABLE ?"
Szczęśliwego kodowania !
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-03-02 10:12:14
A co powiesz na zrzucenie całej bazy danych, a następnie utworzenie jej na nowo? To mi pasuje.
DROP DATABASE mydb;
CREATE DATABASE mydb;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-08-04 09:50:31
Wiem, że to jest stary post teraz, ale próbowałem wszystkie odpowiedzi tutaj na wielu bazach danych i znalazłem wszystkie one działają czasami, ale nie cały czas dla różnych (mogę tylko założyć) dziwactwa SQL Server.
W końcu to wymyśliłem. Testowałem to wszędzie (ogólnie rzecz biorąc), co mogę i działa (bez żadnych ukrytych procedur sklepowych).Dla notatek głównie na SQL Server 2014. (ale większość innych wersji, które wypróbowałem, również wydaje się działać dobrze).
Próbowałem while loops i nulls etc etc, kursory i różne inne formy, ale zawsze wydają się zawodzić w niektórych bazach danych, ale nie w innych bez oczywistego powodu.
Liczenie i używanie go do iteracji zawsze wydaje się działać na wszystkim, co przetestowałem.
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-24 01:05:18
Dla tabele temporalne jest to nieco bardziej skomplikowane ze względu na fakt, że mogą istnieć klucze obce, a także wyjątek:
Drop table operation failed on table XXX because it is not a supported operation on system-versioned temporal tables
Możesz użyć:
-- 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-02-14 11:42:18