Coba ini kalo yg dikasi Pak Nyoman bermasalah dengan dependency.
Script ini saya pake sehari2, dapet nyomot di internet :).
=============================
declare @n char(1)
set @n = char(10)
declare @stmt nvarchar(max)
-- procedures
select @stmt = isnull( @stmt + @n, '' ) +
'drop procedure [' + name + ']'
from sys.procedures
-- check constraints
select @stmt = isnull( @stmt + @n, '' ) +
'alter table [' + object_name( parent_object_id ) + '] drop constraint
[' + name + ']'
from sys.check_constraints
-- functions
select @stmt = isnull( @stmt + @n, '' ) +
'drop function [' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' )
-- views
select @stmt = isnull( @stmt + @n, '' ) +
'drop view [' + name + ']'
from sys.views
-- foreign keys
select @stmt = isnull( @stmt + @n, '' ) +
'alter table [' + object_name( parent_object_id ) + '] drop constraint
[' + name + ']'
from sys.foreign_keys
-- tables
select @stmt = isnull( @stmt + @n, '' ) +
'drop table [' + name + ']'
from sys.tables
-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
'drop type [' + name + ']'
from sys.types
where is_user_defined = 1
exec sp_executesql @stmt
====
On Fri, Jun 11, 2010 at 11:38 AM, I Nyoman Sukaryawan
<[email protected]>wrote:
> pake script aja ;)
>
> BEGIN
> FOR cur_rec IN (SELECT object_name, object_type
> FROM user_objects
> WHERE object_type IN ('TABLE', 'VIEW', 'PACKAGE',
> 'PROCEDURE', 'FUNCTION', 'SEQUENCE'))
> LOOP
> BEGIN
> IF cur_rec.object_type = 'TABLE' THEN
> EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' ||
> cur_rec.object_name || '" CASCADE CONSTRAINTS';
> ELSE
> EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' ||
> cur_rec.object_name || '"';
> END IF;
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "'
> || cur_rec.object_name || '"');
> END;
> END LOOP;
> END;
> /
>
>
>
> On Thu, Jun 10, 2010 at 7:57 PM, Faridh Wisanggeni <
> [email protected]> wrote:
>
> >
> >
> > Dear All,
> >
> > Ada gak sich, query untuk DROP all table,view,package,function dll
> didalam
> > satu user, shingga di usr itu kosong semua...??
> >
> > Thank's before,
> >
> > Faridh Wisanggeni
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> --
> -----------I.N.D.O - O.R.A.C.L.E---------------
> Keluar: [email protected]
> Website: http://indooracle.wordpress.com
> http://www.facebook.com/group.php?gid=51973053515
> -----------------------------------------------
>
> Bergabung dengan Indonesia Thin Client User Groups,
> Terminal Server, Citrix, New Moon Caneveral, di:
> http://indo-thin.blogspot.comYahoo! Groups Links
>
>
>
>
[Non-text portions of this message have been removed]
