Solte todas as tabelas, stored procedures, gatilhos, restrições e todas as dependencies em uma instrução sql

Existe alguma maneira em que eu possa limpar um database no SQl Server 2005, eliminando todas as tabelas e excluindo stored procedures, gatilhos, restrições e todas as dependencies em uma instrução SQL?

RAZÃO PARA PEDIDO:

Eu quero ter um script de database para a limpeza de um database existente que não está em uso, em vez de criar novos, especialmente quando você tem que colocar em um pedido ao administrador do database e esperar por um tempo para fazê-lo!

este script limpa todas as visões, SPS, funções PKs, FKs e tabelas.

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

Eu estou usando este script por Adam Anderson, atualizado para suportar objects em outros esquemas que 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

A melhor coisa a fazer é ” Gerar scripts para Drop

Selecione Banco de Dados -> Clique com o Botão Direito -> Tarefas -> Gerar Scripts – abrirá o assistente para gerar scripts

depois de escolher objects na opção set Scripting, clique em Botão Avançado

  • -> Definir opção ‘Script para criar’ como verdadeiro (quer criar)
  • -> Definir opção ‘ Script to Drop ‘ para true (quer soltar)
  • -> Selecione a checkbox de seleção para selecionar objects que desejam criar script
  • -> Selecione a opção para escrever script (File, New window, Clipboard)
  • Inclui objects dependentes por padrão (e irá eliminar a restrição no início)

    Execute o script

Desta forma, podemos personalizar nosso script.

Para descartar todas as tabelas:

 exec sp_MSforeachtable 'DROP TABLE ?' 

Naturalmente, isso eliminará todas as restrições, acionadores etc., tudo, exceto os stored procedures.

Para os stored procedures, estou com medo de que você precise de outro procedimento armazenado armazenado no master .

Eu faria isso em duas declarações: DROP DATABASE ???

e depois CREATE DATABASE ???

Faça backup de um database completamente vazio. Em vez de eliminar todos os objects, restaure o backup.

Isso é o que eu tentei:

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

O que sempre a saída será impressa, basta copiar tudo e colar em nova consulta e pressione executar. Isso excluirá todas as tabelas.

Eu tentei alguns dos scripts aqui, mas eles não funcionaram para mim, como eu tenho minhas tabelas em esquemas. Então eu coloquei o seguinte. Observe que esse script recebe uma lista de esquemas e, em seguida, cai em sequência. Você precisa ter certeza de que você tem uma ordem completa em seus esquemas. Se houver alguma dependência circular, ela falhará.

 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 

Eu acidentalmente executei um script de boot do database no meu database master hoje à noite. De qualquer forma, eu rapidamente corri para este segmento. Eu usei o: exec sp_MSforeachtable ‘DROP TABLE?’ resposta, mas tive que executá-lo várias vezes até que ele não tenha erro (dependencies). Depois disso, tropecei em alguns outros tópicos e juntei isso para eliminar todos os procedimentos e funções armazenados.

 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 

Tente isso

 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' 

Além da resposta do @ Ivan, todos os tipos precisam ser incluídos

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

Não existe uma afirmação única que possa ser usada para alcançar este objective.

Você poderia, é claro, criar um stored procedure que poderia ser usado para executar essas várias tarefas administrativas.

Você poderia então executar o procedimento usando esta única instrução.

 Exec sp_CleanDatabases @DatabaseName='DBname' 

Parece um recurso bastante perigoso para mim. Se você implementasse algo assim, eu me certificaria de protegê-lo de uma maneira que você não possa executar isso por acidente.

Como sugerido antes, você poderia fazer algum tipo de procedimento armazenado por conta própria. No SQL Server 2005, você pode dar uma olhada nesta tabela do sistema para determinar e encontrar os objects que deseja soltar.

 select * from sys.objects 

Aqui eu encontrei uma nova consulta para excluir todos os sp, funções e gatilhos

 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 

Para adicionar a resposta de Ivan, também tive a necessidade de descartar todos os tipos definidos pelo usuário, então adicionei isso ao 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 

Você precisa desabilitar todos os triggers e constraints primeiro.

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

Depois disso, você pode gerar os scripts para excluir os objects como

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

Execute as instruções geradas.

Para remover todos os objects no oracle:

1) Dinâmico

 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) estático

  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'); 

tente isso ….

 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