Generate Drop / Create Script for views, stored Procedures

,

This Script generates Drop and Create script for either views or stored procedures.

Works currently only on SQL Server 2005

DECLARE @DATABASE AS VARCHAR(20)
SET @DATABASE = 'AdventureWorks' -- EX: AdventureWorks

DECLARE @WHATTOCREATE AS CHAR(1)
SET @WHATTOCREATE = 'P' -- EX: p = procedures, v = views

EXEC ('USE ' +@DATABASE)

PRINT 'USE [' + @DATABASE + ']' + CHAR(13) + 'GO' + CHAR(13)

DECLARE JOB_CURSOR CURSOR FOR
SELECT OBJECT_ID FROM SYS.OBJECTS WHERE [TYPE] IN (@WHATTOCREATE)
OPEN JOB_CURSOR
DECLARE @OBJECT_ID AS INT
FETCH NEXT FROM JOB_CURSOR
INTO @OBJECT_ID

DECLARE @SQL AS VARCHAR(MAX)

    WHILE @@FETCH_STATUS = 0
    BEGIN
        
        SELECT @SQL = 'IF OBJECT_ID (''['+ S.NAME + '].['+ O.NAME + ']'') IS NOT NULL 
BEGIN
    DROP '+
        CASE O.TYPE 
            WHEN 'P' THEN 'PROCEDURE ' 
            WHEN 'V' THEN 'VIEW '
        END + ' ['+ S.NAME + '].['+ O.NAME + ']
END
GO' + CHAR(13) + CHAR(13) +
                    + M.DEFINITION + CHAR(13) + 'GO' + CHAR(13)
                        FROM SYS.OBJECTS O 
                        INNER JOIN SYS.SQL_MODULES M ON O.OBJECT_ID = M.OBJECT_ID 
                        INNER JOIN SYS.SCHEMAS S ON S.SCHEMA_ID = O.SCHEMA_ID
                        WHERE O.OBJECT_ID = @OBJECT_ID
        
        PRINT @SQL

     FETCH NEXT FROM JOB_CURSOR
     INTO @OBJECT_ID
    END

CLOSE JOB_CURSOR
DEALLOCATE JOB_CURSOR

Rate

2.44 (9)

Share

Share

Rate

2.44 (9)