By the way, in order to find out more about deadlocks : http://www.sql-server-performance.com/tips/deadlocks_p1.aspx
* To DROP all tables (run it many times so that tables with constraints are progressively destroyed)
EXEC sp_MSforeachtable "DROP TABLE ? PRINT '? to be dropped' "
* Here is to DROP ALL SP and VIEWS
CREATE PROCEDURE Usp_DropAllSPViews
AS
DECLARE @name varchar(100)
DECLARE @xtype char(1)
DECLARE @sqlstring nvarchar(1000)
DECLARE AllSPViews_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 AllSPViews_cursor
FETCH NEXT FROM SPViews_cursor INTO @name, @xtype
WHILE @@fetch_status = 0
BEGIN
-- obtain object type IF it is a stored procedure or view
IF @xtype = 'P'
BEGIN
SET @sqlstring = 'drop procedure ' + @name
EXEC sp_executesql @sqlstring
SET @sqlstring = ' '
END
-- obtain object type IF it is a view or stored procedure
IF @xtype = 'V'
BEGIN
SET @sqlstring = 'drop view ' + @name
EXEC sp_executesql @sqlstring
SET @sqlstring = ' '
END
FETCH NEXT FROM AllSPViews_cursor INTO @name, @xtype
END
CLOSE AllSPViews_cursor
DEALLOCATE AllSPViews_cursor
