MS SQL Drop All table

mssql_logo

I deleted all table in ms sql with:
exec sp_MSforeachtable “DROP TABLE ? PRINT ‘? dropped’ “

very useful if you want to create new table.

And then used the following statements to drop procedures:
create procedure DropSPViews
as

— variable to object name
declare @name varchar(100)
— variable to hold object type
declare @xtype char(1)
— variable to hold sql string
declare @sqlstring nvarchar(1000)

declare SPViews_cursor cursor for
SELECT sysobjects.name, sysobjects.xtype
FROM sysobjects
join sysusers on sysobjects.uid = sysusers.uid
where OBJECTPROPERTY(sysobjects.id, N’IsProcedure’) = 1
or OBJECTPROPERTY(sysobjects.id, N’IsView’) = 1 and sysusers.name =
‘USERNAME’

open SPViews_cursor

fetch next from SPViews_cursor into @name, @xtype

while @@fetch_status = 0
begin
— test object type if it is a stored procedure
if @xtype = ‘P’
begin
set @sqlstring = ‘drop procedure ‘ + @name
exec sp_executesql @sqlstring
set @sqlstring = ‘ ‘
end
— test object type if it is a view
if @xtype = ‘V’
begin
set @sqlstring = ‘drop view ‘ + @name
exec sp_executesql @sqlstring
set @sqlstring = ‘ ‘
end

— get next record
fetch next from SPViews_cursor into @name, @xtype
end

close SPViews_cursor
deallocate SPViews_cursor

original source

Advertisements

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