GO
---drop all procedure in a database in sql server
declare @userProcedureName varchar(2000)
declare drop_proc_cur cursor
for select [name] from sys.objects where type = 'p'
open drop_proc_cur
fetch next from drop_proc_cur into @userProcedureName
while @@fetch_status = 0
begin
--print @userProcedureName;
exec('drop procedure [' + @userProcedureName + ']')
fetch next from drop_proc_cur into @userProcedureName
end
close drop_proc_cur
deallocate drop_proc_cur
GO
GO
-----drop all user view in sql server database
DECLARE @sqlExecute VARCHAR(MAX) = ''
, @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;
SELECT @sqlExecute = @sqlExecute + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM sys.views v
--PRINT @sqlExecute;
EXEC(@sqlExecute);
GO
--drop all function in sql server database
Declare @sqlExecute NVARCHAR(MAX) = N'';
SELECT @sqlExecute = @sqlExecute + N' DROP FUNCTION '
+ QUOTENAME(SCHEMA_NAME(schema_id))
+ N'.' + QUOTENAME(name)
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
print @sqlExecute
Exec sp_executesql @sqlExecute
GO
----drop all user-defined types from sql server database
declare @sqlExecute12 NVARCHAR(MAX) = N'';
SELECT @sqlExecute12 = @sqlExecute12 + N' DROP type '
+ QUOTENAME(SCHEMA_NAME(schema_id))
+ N'.' + QUOTENAME(name)
FROM sys.types
WHERE is_user_defined = 1
--print @sqlExecute;
Exec sp_executesql @sqlExecute12
