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
10 comments:
This was very cool and saved me lot of time. Thanks for the hint!
Nikolay
Another Anon says "Thanks Mate"
Great - thanks for this :)
Wow thanks a bunch for this!
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.
This is great! Just saved me hours of work.
Thanks for sharing.
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
Great :) Thanks a ton !!!!!
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)
Thank RUSLAN TRIFONOV and nsimeonov. I saved me.
Post a Comment