purpose of life is joy

NAVIGATION - SEARCH

How to : delete all stored procedures, views, functions and tables from MS SQL?

I forcefully happen to write this post. At my work place, I don't have rights to create/drop the databases only DBAs have that privilege and process involved in that is also time consuming. Hence I have opportunity to create a SP to make it quicker and smarter. This SP has been created with the help of www. Basically, below Stored Procedure will deletes the all stored procedures (SP), views, functions, table constrains and finally tables from a database in a minute. you can alter this SP where in you can delete other objects under '-- you can add custom types here' section.


PS: be careful while executing this SP. It will completely revamp the whole database without warning.

 

CREATE PROCEDURE sp_EmptyDatabase AS
DECLARE @ObjectName  VARCHAR(1000)
DECLARE @ObjectType VARCHAR(20)
DECLARE @SQLQuery NVARCHAR(4000)
DECLARE SPViews_CURSOR CURSOR FOR
SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS name, ROUTINE_TYPE AS xtype FROM INFORMATION_SCHEMA.ROUTINES
UNION
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS name, 'VIEW' AS xtype FROM INFORMATION_SCHEMA.VIEWS
 
OPEN SPViews_CURSOR
FETCH NEXT FROM SPViews_CURSOR INTO @ObjectName, @ObjectType
WHILE @@fetch_status = 0
BEGIN
    --Deleting all the procedures
    IF @ObjectType = 'PROCEDURE'
    BEGIN
        SET @SQLQuery = 'DROP PROCEDURE ' + @ObjectName
        IF(@ObjectName!=OBJECT_NAME(@@PROCID) OR @ObjectName!='sp_EmptyDatabase')
        BEGIN
            EXEC sp_EXECutesql @SQLQuery
        END
        SET @SQLQuery = ' '
    END
     
    --Deleting all the functions
    IF @ObjectType = 'FUNCTION'
    BEGIN
        SET @SQLQuery = 'DROP FUNCTION ' + @ObjectName
        EXEC sp_EXECutesql @SQLQuery
        SET @SQLQuery = ' '
    END
     
    --Deleting all the views
    IF @ObjectType = 'VIEW'
    BEGIN
        SET @SQLQuery = 'DROP VIEW ' + @ObjectName
        EXEC sp_EXECutesql @SQLQuery
        SET @SQLQuery = ' '
    END
    FETCH NEXT FROM SPViews_CURSOR INTO @ObjectName, @ObjectType
END
 
CLOSE SPViews_CURSOR
DEALLOCATE SPViews_CURSOR
 
--Deleting all the constrains
DECLARE @CURSOR CURSOR
SET @CURSOR = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc.TABLE_NAME + '] DROP [' + rc.CONSTRAINT_NAME + ']' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
                       LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME =rc.CONSTRAINT_NAME
 
OPEN @CURSOR FETCH NEXT FROM @CURSOR INTO @SQLQuery
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC SP_EXECUTESQL @SQLQuery
    FETCH NEXT FROM @CURSOR INTO @SQLQuery
END
CLOSE @CURSOR DEALLOCATE @CURSOR
GO
--Deleting all the tables
EXEC sp_MSFOREachTable 'DROP TABLE ?'
GO

References : http://blogs.msdn.com/b/patrickgallucci/archive/2008/04/29/how-to-drop-all-tables-all-views-and-all-stored-procedures-from-a-sql-2005-db.aspx

blog comments powered by Disqus
Protected by Copyscape Web Plagiarism Check
DMCA.com