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

Monday, April 24, 2006

NET Compact Framework 2.0 SP1 Beta is out

.NET Compact Framework 2.0 SP1 Beta is now available! See more details at the Compact Framework Team's blog.
There are number of new features and a lot of bug fixes. The long waited WinCe 4.2 support is already a fact! Another great new feature is the
.Net Compact Framework Remote Performance Monitor. It may be downloaded here

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