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
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


this is the original source

11 comments:

Anonymous said...

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

Nikolay

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)
begin
   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 = c1.id and c1.colid=k.fkey)
               join syscolumns c2 on (k.rkeyid = c2.id and c2.colid=k.rkey)
            where c2.id = o2.id and c1.id <> o2.id
         )
   open table_list
   fetch next from table_list into @table_name   
   while @@fetch_status = 0
   begin
      print 'dropping table '+@table_name
      exec ('drop table '+@table_name)   
      fetch next from table_list into @table_name
   end   
   close table_list
   deallocate table_list
   set @i = @i+1
end

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.