Eliminare tutte le tabelle, stored procedure, trigger, vincoli e tutte le dipendenze in una istruzione sql

C’è un modo in cui posso pulire un database in SQl Server 2005 eliminando tutte le tabelle ed eliminando stored procedure, trigger, vincoli e tutte le dipendenze in un’unica istruzione SQL?

MOTIVO DELLA RICHIESTA:

Voglio avere uno script DB per ripulire un DB esistente che non è in uso piuttosto che crearne di nuovi, specialmente quando devi mettere una richiesta all’amministratore del tuo DB e aspettare un po ‘per averlo fatto!

questo script pulisce tutte le viste, SPS, funzioni PK, FK e tabelle.

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

Sto usando questo script di Adam Anderson, aggiornato per supportare gli oggetti in altri schemi rispetto a dbo.

 declare @n char(1) set @n = char(10) declare @stmt nvarchar(max) -- procedures select @stmt = isnull( @stmt + @n, '' ) + 'drop procedure [' + schema_name(schema_id) + '].[' + name + ']' from sys.procedures -- check constraints select @stmt = isnull( @stmt + @n, '' ) + 'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']' from sys.check_constraints -- functions select @stmt = isnull( @stmt + @n, '' ) + 'drop function [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects where type in ( 'FN', 'IF', 'TF' ) -- views select @stmt = isnull( @stmt + @n, '' ) + 'drop view [' + schema_name(schema_id) + '].[' + name + ']' from sys.views -- foreign keys select @stmt = isnull( @stmt + @n, '' ) + 'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']' from sys.foreign_keys -- tables select @stmt = isnull( @stmt + @n, '' ) + 'drop table [' + schema_name(schema_id) + '].[' + name + ']' from sys.tables -- user defined types select @stmt = isnull( @stmt + @n, '' ) + 'drop type [' + schema_name(schema_id) + '].[' + name + ']' from sys.types where is_user_defined = 1 exec sp_executesql @stmt 

Fonte: http://blog.falafel.com/Blogs/AdamAnderson/09-01-06/T-SQL_Drop_All_Objects_in_a_SQL_Server_Database.aspx

La cosa migliore da fare è ” Genera script per Drop

Seleziona Database -> Clic destro -> Attività -> Genera script – aprirà la procedura guidata per la generazione di script

dopo aver scelto gli oggetti nell’opzione Scripting set fai clic su Advanced Button

  • -> Imposta l’opzione ‘Script da creare’ su true (vuoi creare)
  • -> Imposta l’opzione ‘ Script to Drop ‘ su true (vuoi eliminare)
  • -> Seleziona la casella di controllo per selezionare gli oggetti che desiderano creare uno script
  • -> Seleziona la scelta per scrivere uno script (File, Nuova finestra, Appunti)
  • Include gli oggetti dipendenti per impostazione predefinita (e per prima cosa eliminerà il vincolo)

    Esegui lo script

In questo modo possiamo personalizzare il nostro script.

Per eliminare tutte le tabelle:

 exec sp_MSforeachtable 'DROP TABLE ?' 

Questo, naturalmente, eliminerà tutti i vincoli, i trigger ecc., Tutto tranne le stored procedure.

Per le stored procedure temo che ti occorrerà un’altra stored procedure memorizzata nel master .

Lo farei in due dichiarazioni: DROP DATABASE ???

e quindi CREATE DATABASE ???

Eseguire il backup di un database completamente vuoto. Invece di eliminare tutti gli oggetti, è sufficiente ripristinare il backup.

Questo è quello che ho provato:

 SELECT 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.tables 

Qualunque sia l’output che stamperà, copia e incolla tutto nella nuova query e premi execute. Questo cancellerà tutte le tabelle.

Ho provato alcuni degli script qui, ma non hanno funzionato per me, dato che ho le mie tabelle in schemi. Quindi ho messo insieme quanto segue. Si noti che questo script prende un elenco di schemi e quindi scade in sequenza. Devi assicurarti di avere un ordine completo nei tuoi schemi. Se ci sono delle dipendenze circolari, allora fallirà.

 PRINT 'Dropping whole database' GO ------------------------------------------ -- Drop constraints ------------------------------------------ DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT sql = 'ALTER TABLE ['+tc2.CONSTRAINT_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 PRINT @Sql Exec (@Sql) FETCH NEXT FROM @Cursor INTO @Sql END CLOSE @Cursor DEALLOCATE @Cursor GO ------------------------------------------ -- Drop views ------------------------------------------ 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); GO ------------------------------------------ -- Drop procs ------------------------------------------ PRINT 'Dropping all procs ...' GO DECLARE @sql VARCHAR(MAX) = '' , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ; SELECT @sql = @sql + 'DROP PROC ' + QUOTENAME(SCHEMA_NAME(p.schema_id)) + '.' + QUOTENAME(p.name) +';' + @crlf FROM [sys].[procedures] p PRINT @sql; EXEC(@sql); GO ------------------------------------------ -- Drop tables ------------------------------------------ PRINT 'Dropping all tables ...' GO EXEC sp_MSForEachTable 'DROP TABLE ?' GO ------------------------------------------ -- Drop sequences ------------------------------------------ PRINT 'Dropping all sequences ...' GO DECLARE @DropSeqSql varchar(1024) DECLARE DropSeqCursor CURSOR FOR SELECT DISTINCT 'DROP SEQUENCE ' + s.SEQUENCE_SCHEMA + '.' + s.SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES s OPEN DropSeqCursor FETCH NEXT FROM DropSeqCursor INTO @DropSeqSql WHILE ( @@FETCH_STATUS <> -1 ) BEGIN PRINT @DropSeqSql EXECUTE( @DropSeqSql ) FETCH NEXT FROM DropSeqCursor INTO @DropSeqSql END CLOSE DropSeqCursor DEALLOCATE DropSeqCursor GO ------------------------------------------ -- Drop Schemas ------------------------------------------ DECLARE @schemas as varchar(1000) = 'StaticData,Ird,DataImport,Collateral,Report,Cds,CommonTrade,MarketData,TypeCode' DECLARE @schemasXml as xml = cast((''+replace(@schemas,',' ,'')+'') as xml) DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT sql = 'DROP SCHEMA ['+schemaName+']' FROM (SELECT CAST(T.schemaName.query('text()') as VARCHAR(200)) as schemaName FROM @schemasXml.nodes('/schema') T(schemaName)) as X JOIN information_schema.schemata S on S.schema_name = X.schemaName OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @Sql Exec (@Sql) FETCH NEXT FROM @Cursor INTO @Sql END CLOSE @Cursor DEALLOCATE @Cursor GO 

Ho accidentalmente eseguito uno script db init sul mio database master stasera. Ad ogni modo, ho subito trovato questo thread. Ho usato: exec sp_MSforeachtable ‘DROP TABLE?’ risposta, ma ha dovuto eseguirlo più volte fino a quando non ha commesso errori (dipendenze). Dopo di che sono incappato in alcuni altri thread e ho messo insieme questo per eliminare tutte le stored procedure e le funzioni.

 DECLARE mycur CURSOR FOR select O.type_desc,schema_id,O.name from sys.objects O LEFT OUTER JOIN sys.extended_properties E ON O.object_id = E.major_id WHERE O.name IS NOT NULL AND ISNULL(O.is_ms_shipped, 0) = 0 AND ISNULL(E.name, '') <> 'microsoft_database_tools_support' AND ( O.type_desc = 'SQL_STORED_PROCEDURE' OR O.type_desc = 'SQL_SCALAR_FUNCTION' ) ORDER BY O.type_desc,O.name; OPEN mycur; DECLARE @schema_id int; DECLARE @fname varchar(256); DECLARE @sname varchar(256); DECLARE @ftype varchar(256); FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname; WHILE @@FETCH_STATUS = 0 BEGIN SET @sname = SCHEMA_NAME( @schema_id ); IF @ftype = 'SQL_STORED_PROCEDURE' EXEC( 'DROP PROCEDURE "' + @sname + '"."' + @fname + '"' ); IF @ftype = 'SQL_SCALAR_FUNCTION' EXEC( 'DROP FUNCTION "' + @sname + '"."' + @fname + '"' ); FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname; END CLOSE mycur DEALLOCATE mycur GO 

Prova questo

 Select 'ALTER TABLE ' + Table_Name +' drop constraint ' + Constraint_Name from Information_Schema.CONSTRAINT_TABLE_USAGE Select 'drop Procedure ' + specific_name from Information_Schema.Routines where specific_name not like 'sp%' AND specific_name not like 'fn_%' Select 'drop View ' + table_name from Information_Schema.tables where Table_Type = 'VIEW' SELECT 'DROP TRIGGER ' + name FROM sysobjects WHERE type = 'tr' Select 'drop table ' + table_name from Information_Schema.tables where Table_Type = 'BASE TABLE' 

Oltre alla risposta di @ Ivan, tutti i tipi devono essere inclusi

  /* Drop all Types */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sys.types where is_user_defined = 1 ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP TYPE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Type: ' + @name SELECT @name = (SELECT TOP 1 [name] FROM sys.types where is_user_defined = 1 AND [name] > @name ORDER BY [name]) END GO 

Non esiste una singola dichiarazione che possa essere utilizzata per raggiungere questo objective.

Ovviamente è ansible creare una stored procedure che è ansible utilizzare per eseguire queste varie attività amministrative.

È quindi ansible eseguire la procedura utilizzando questa singola istruzione.

 Exec sp_CleanDatabases @DatabaseName='DBname' 

Sembra una caratteristica piuttosto pericolosa per me. Se dovessi implementare qualcosa di simile, assicurerei di proteggerlo correttamente in modo da non essere in grado di eseguirlo per incidente.

Come suggerito prima di poter fare una sorta di stored procedure da soli. In SQL Server 2005 è ansible dare un’occhiata a questa tabella di sistema per determinare e trovare gli oggetti che si desidera eliminare.

 select * from sys.objects 

Qui ho trovato una nuova query per eliminare tutte le sp, le funzioni e i trigger

 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 

Per aggiungere alla risposta di Ivan, ho anche avuto la necessità di eliminare tutti i tipi definiti dall’utente, quindi l’ho aggiunto allo script:

 /* Drop all user-defined types */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (select TOP 1 [name] from sys.types where is_user_defined = 1) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP TYPE [dbo].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped Type: ' + @name SELECT @name = (select TOP 1 [name] from sys.types where is_user_defined = 1) END GO 
 DECLARE @name VARCHAR(255) DECLARE @type VARCHAR(10) DECLARE @prefix VARCHAR(255) DECLARE @sql VARCHAR(255) DECLARE curs CURSOR FOR SELECT [name], xtype FROM sysobjects WHERE xtype IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR') -- Configuration point 1 ORDER BY name OPEN curs FETCH NEXT FROM curs INTO @name, @type WHILE @@FETCH_STATUS = 0 BEGIN -- Configuration point 2 SET @prefix = CASE @type WHEN 'U' THEN 'DROP TABLE' WHEN 'P' THEN 'DROP PROCEDURE' WHEN 'FN' THEN 'DROP FUNCTION' WHEN 'IF' THEN 'DROP FUNCTION' WHEN 'TF' THEN 'DROP FUNCTION' WHEN 'V' THEN 'DROP VIEW' WHEN 'TR' THEN 'DROP TRIGGER' END SET @sql = @prefix + ' ' + @name PRINT @sql EXEC(@sql) FETCH NEXT FROM curs INTO @name, @type END CLOSE curs DEALLOCATE curs 

Devi prima disabilitare tutti i triggers e i constraints .

 EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL" EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL" 

Successivamente è ansible generare gli script per l’eliminazione degli oggetti come

 SELECT 'Drop Table '+name FROM sys.tables WHERE type='U'; SELECT 'Drop Procedure '+name FROM sys.procedures WHERE type='P'; 

Esegui le istruzioni generate.

Per rimuovere tutti gli oggetti in oracle:

1) Dinamico

 DECLARE CURSOR IX IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE ='TABLE' AND OWNER='SCHEMA_NAME'; CURSOR IY IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION', 'VIEW') AND OWNER='SCHEMA_NAME'; CURSOR IZ IS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND OWNER='SCHEMA_NAME'; BEGIN FOR X IN IX LOOP EXECUTE IMMEDIATE('DROP '||X.OBJECT_TYPE||' '||X.OBJECT_NAME|| ' CASCADE CONSTRAINT'); END LOOP; FOR Y IN IY LOOP EXECUTE IMMEDIATE('DROP '||Y.OBJECT_TYPE||' '||Y.OBJECT_NAME); END LOOP; FOR Z IN IZ LOOP EXECUTE IMMEDIATE('DROP '||Z.OBJECT_TYPE||' '||Z.OBJECT_NAME||' FORCE '); END LOOP; END; / 

2) statico

  SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables union ALL select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION') union ALL SELECT 'drop ' ||object_type ||' ' || object_name || ' force;' FROM user_objects WHERE object_type IN ('TYPE'); 

prova questo….

 USE DATABASE GO DECLARE @tname VARCHAR(150) DECLARE @strsql VARCHAR(300) SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' and [name] like N'TableName%' ORDER BY [name]) WHILE @tname IS NOT NULL BEGIN SELECT @strsql = 'DROP TABLE [dbo].[' + RTRIM(@tname) +']' EXEC (@strsql) PRINT 'Dropped Table : ' + @tname SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' AND [name] like N'TableName%' AND [name] > @tname ORDER BY [name]) END