NAVmoble - the pocket-sized ERP
Optimized for Microsoft Dynamics NAV and Windows Mobile powered devices

Thursday, April 06, 2006

How to drop all tables from a database(MSSQL)

I had a problem upgrading one of may dotnetnuke based portals and I needed to start over with a fresh database. Due to some hosting issues I couldn't drop and recreate the database. I had a large number of objects and needed a quick magic sentence to drop them all. fortunately the following ones did the job:

I deleted all table with:
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' "

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

-- 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.xtype
FROM sysobjects
join sysusers on sysobjects.uid = sysusers.uid
where OBJECTPROPERTY(, N'IsProcedure') = 1
or OBJECTPROPERTY(, N'IsView') = 1 and =

open SPViews_cursor

fetch next from SPViews_cursor into @name, @xtype

while @@fetch_status = 0
-- test object type if it is a stored procedure
if @xtype = 'P'
set @sqlstring = 'drop procedure ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '
-- test object type if it is a view
if @xtype = 'V'
set @sqlstring = 'drop view ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '

-- get next record
fetch next from SPViews_cursor into @name, @xtype

close SPViews_cursor
deallocate SPViews_cursor

this is the original source


Anonymous said...

This was very cool and saved me lot of time. Thanks for the hint!


Anonymous said...

Thank you.

Anonymous said...

Another Anon says "Thanks Mate"

quiksilv said...

Great - thanks for this :)

Anonymous said...

Wow thanks a bunch for this!

Colin said...

Years later you are still saving people time trying to do all this stuff manually.

exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' "

That's exactly what I needed. Thank you sir.

niclar said...

This is great! Just saved me hours of work.
Thanks for sharing.

nsimeonov said...

In order to drop tables when foreign keys are present and to avoid getting errors I would recommend using the following script:

declare @i int
set @i = 0
while (@i<99)
   declare @table_name varchar(100)
   declare table_list cursor for
      select name from sysobjects o2 where xtype='U' and
         not exists (
            select * from sysforeignkeys k
               join syscolumns c1 on (k.fkeyid = and c1.colid=k.fkey)
               join syscolumns c2 on (k.rkeyid = and c2.colid=k.rkey)
            where = and <>
   open table_list
   fetch next from table_list into @table_name   
   while @@fetch_status = 0
      print 'dropping table '+@table_name
      exec ('drop table '+@table_name)   
      fetch next from table_list into @table_name
   close table_list
   deallocate table_list
   set @i = @i+1

Anonymous said...

Great :) Thanks a ton !!!!!

Tom Chapin said...

try this

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

this is a hidden SP in sql server, this will be executed for each table in the database you connected (you cant rollback this)

Anonymous said...

Thank RUSLAN TRIFONOV and nsimeonov. I saved me.