Friday, March 13, 2009

Destroy ALL tables, views or stored procedures

If you want to destroy ALL tables, views or stored procedures, here are some T-SQL commands.
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

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.