March 8, 2013

Generate Script of stored procedure using Cursor


Result set will contain changes done on date 07/03/2013  and below cursor will generate script of all the stored procedure in result set


SET NOCOUNT ON;

DECLARE @Objectid INT,
        @Spname   NVARCHAR(80),
        @Message  VARCHAR(5000)
DECLARE SpBackupCursor CURSOR FOR
  SELECT Name AS SPName,
         Object_id
    FROM sys.objects
   WHERE Type = 'p'
         AND CONVERT(VARCHAR(10), Modify_date, 112) = '20130307'
   ORDER BY Modify_date DESC

OPEN SpBackupCursor

FETCH NEXT FROM SpBackupCursor INTO @Spname, @Objectid

WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT '  Go '

      EXEC Sp_helptext
        @Spname

      -- Get the next vendor.
      FETCH NEXT FROM SpBackupCursor INTO @Spname, @Objectid
  END

CLOSE SpBackupCursor;

DEALLOCATE SpBackupCursor;

No comments:

Post a Comment