Thursday, December 30, 2010

How to drop all tables from a SQL Server 2005 Database

How to drop all tables, all views, and all stored procedures
from a SQL Server 2005 Database?
It may not be a hardcore requirement on day-to-day basis to drop all tables, views and stored
procedures from a SQL Server database within your environment, but it will be handy to have
such a code at your end when such task is required.
There are 2 ways to accomplish this, first using undocumented stored procedure such as
'sp_MSforeachtable' as follows:
exec sp_MSforeachtable "DROP TABLE ? PRINT '? to be dropped' "
Where the results will have all of the tables to be dropped, ok how about
for views & stored procedure then. Here it goes:
create procedure Usp_DropAllSPViews
declare @name  varchar(100)
declare @xtype char(1)
declare @sqlstring nvarchar(1000)
declare AllSPViews_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 ='USERNAME'
open AllSPViews_cursor
fetch next from SPViews_cursor into @name, @xtype
while @@fetch_status = 0
-- obtain object type if it is a stored procedure or view
   if @xtype = 'P'
        set @sqlstring = 'drop procedure ' + @name
        exec sp_executesql @sqlstring
        set @sqlstring = ' '
-- obtain object type if it is a view or stored procedure
   if @xtype = 'V'
         set @sqlstring = 'drop view ' + @name
         exec sp_executesql @sqlstring
         set @sqlstring = ' '
    fetch next from AllSPViews_cursor into @name, @xtype
close AllSPViews_cursor
deallocate AllSPViews_cursor

Always test above script within your test or sample database and be satisfied with results to check,
do not directly attempt on a live database that I will not give you any warranty or guarantee on
above task. Do not forget to have a

No comments :

Post a Comment