PranasLu's Blog

2011-07-12

TSQL script to delete all MSSQL schema objects

Filed under: Programming — Tags: , , — pranaslu @ 18:29

declare @schema varchar(200)
select @schema = ‘AUTH’

DECLARE DeleteStatements CURSOR LOCAL STATIC FOR
select
‘DROP ‘ + case
    when o.xtype = ‘U’ then ‘TABLE’
    when o.xtype = ‘V’ then ‘VIEW’
    when o.xtype = ‘P’ then ‘PROCEDURE’
    when o.xtype = ‘FN’ then ‘FUNCTION’
    end + ‘ ‘ + s.name + ‘.’ + o.name as SQL
from
    sys.sysobjects as o
    join sys.schemas as s on o.uid = s.schema_id
where
    s.name = @schema and
    o.xtype in (‘U’,’V’,’P’,’FN’)
   
DECLARE @Statement nvarchar(4000) 
OPEN DeleteStatements 
 
WHILE 1=1 BEGIN
    FETCH NEXT FROM DeleteStatements INTO @Statement 
    IF @@FETCH_STATUS<>0 BREAK 
     
    EXEC (@Statement) 
END
 
CLOSE DeleteStatements 
DEALLOCATE DeleteStatements 

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: