valor stored salida retornar resueltos procedimientos procedimiento parametros para mostrar entrada ejercicios ejemplos ejecutar devolver datos con almacenados almacenado sql sql-server sql-server-2005 tsql

salida - retornar valor stored procedure sql server



Descarte todas las tablas, procedimientos almacenados, desencadenadores, restricciones y todas las dependencias en una declaraciĆ³n de SQL. (19)

Accidentalmente ejecuté un script de inicio de db contra mi base de datos maestra esta noche. De todos modos, rápidamente me encontré con este hilo. Utilicé: exec sp_MSforeachtable ''DROP TABLE?'' Responda, pero tuve que ejecutarlo varias veces hasta que no se produjo el error (dependencias). Después de eso, me topé con algunos otros subprocesos y junté esto para eliminar todos los procedimientos y funciones almacenados.

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

¿Hay alguna forma en la que pueda limpiar una base de datos en SQl Server 2005 eliminando todas las tablas y eliminando procedimientos almacenados, desencadenadores, restricciones y todas las dependencias en una declaración SQL?

RAZÓN DE LA SOLICITUD:

¡Quiero tener un script de base de datos para limpiar una base de datos existente que no está en uso en lugar de crear otras nuevas, especialmente cuando tiene que enviar una solicitud a su administrador de base de datos y esperar un tiempo para hacerlo!


Además de la respuesta de @ Ivan, todos los tipos deben incluirse

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


Aquí encontré una nueva consulta para eliminar todos los sp, funciones y disparadores

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


Copia de seguridad de una base de datos completamente vacía. En lugar de eliminar todos los objetos, simplemente restaure la copia de seguridad.


Esto es lo que he intentado:

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

Cualquiera que sea la salida que imprimirá, simplemente copie todo, pegue la nueva consulta y presione ejecutar. Esto borrará todas las tablas.


Estoy usando este script de Adam Anderson, actualizado para admitir objetos en otros esquemas que no sean 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

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


Intenté algunas secuencias de comandos aquí, pero no funcionaron para mí, ya que tengo mis tablas en esquemas. Así que armé lo siguiente. Tenga en cuenta que esta secuencia de comandos toma una lista de esquemas, y luego cae en secuencia. Debe asegurarse de tener un orden completo en sus esquemas. Si hay dependencias circulares, entonces fallará.

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((''<schema>''+replace(@schemas,'','' ,''</schema><schema>'')+''</schema>'') 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



Lo mejor que puedes hacer es " Generar scripts para Drop "

Seleccione Base de datos -> Clic derecho -> Tareas -> Generar scripts - se abrirá el asistente para generar scripts

después de elegir los objetos en la opción establecer secuencias de comandos, haga clic en el botón Avanzado

  • -> Establezca la opción ''Script para crear'' en verdadero (desea crear)
  • -> Establezca la opción '' Script to Drop '' en true (desea soltar)
  • -> Seleccione la casilla de verificación para seleccionar los objetos que desea crear script
  • -> Seleccione la opción para escribir guión (Archivo, Nueva ventana, Portapapeles)
  • Incluye objetos dependientes de forma predeterminada (y eliminará la restricción al principio)

    Ejecutar el guion

De esta manera podemos personalizar nuestro script.


Me parece una característica bastante peligrosa para mí. Si implementara algo como esto, me aseguraría de asegurarlo adecuadamente de una manera que no pueda ejecutar esto por accidente.

Como se sugirió antes, usted mismo podría hacer algún tipo de procedimiento almacenado. En SQL Server 2005, puede ver esta tabla del sistema para determinar y encontrar los objetos que desea eliminar.

select * from sys.objects


No hay una sola declaración que se pueda utilizar para lograr este objetivo.

Por supuesto, podría crearse un stored procedure que podría utilizar para realizar estas diversas tareas administrativas.

A continuación, puede ejecutar el procedimiento utilizando esta única instrucción.

Exec sp_CleanDatabases @DatabaseName=''DBname''


Para agregar a la respuesta de Ivan, también tuve la necesidad de eliminar todos los tipos definidos por el usuario, así que agregué esto al 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


Para eliminar todas las tablas:

exec sp_MSforeachtable ''DROP TABLE ?''

Esto, por supuesto, eliminará todas las restricciones, desencadenadores, etc., todo menos los procedimientos almacenados.

Para los procedimientos almacenados, me temo que necesitará otro procedimiento almacenado almacenado en el master .


Para eliminar todos los objetos en 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'');


Prueba esto

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


este script limpia todas las vistas, SPS, funciones PKs, FKs y tablas.

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


prueba esto....

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


Primero debes deshabilitar todos los triggers y constraints .

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

Después de eso, puede generar los scripts para eliminar los objetos como

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

Ejecutar las sentencias generadas.


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