Wednesday, July 1, 2015

How to create scripts of stored procedure modified on specific period

SET NOCOUNT ON;
DECLARE @HelpText TABLE
(
    Val NVARCHAR(MAX)
);

DECLARE @sp_names TABLE
(
    ID INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(128)
);

DECLARE @sp_count INT,
        @count INT = 0,
        @sp_name NVARCHAR(128),
        @text NVARCHAR(MAX);

INSERT  @sp_names
SELECT  name
FROM    sys.Procedures  where name like '%salary%'  and modify_date between '2015-06-22 12:17:03.860' and '2015-07-01 11:38:35.187'

SET @sp_count = (SELECT COUNT(1) FROM sys.Procedures where name like '%salary%'  and modify_date between '2015-06-22 12:17:03.860' and '2015-07-01 11:38:35.187');

WHILE (@sp_count > @count)
BEGIN
    SET @count = @count + 1;
    SET @text = N'';

    SET @sp_name = (SELECT  name
                    FROM    @sp_names
                    WHERE   ID = @count);

    INSERT INTO @HelpText
    EXEC sp_HelpText @sp_name;

    SELECT  @text = COALESCE(@text + ' ' + Val, Val)
    FROM    @HelpText;
  declare @dropIfExists varchar(250) = '  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@sp_name+']'') AND type in (N''P'', N''PC''))
     DROP PROCEDURE [dbo].['+@sp_name+'] '
     PRINT 'GO'
     print @dropIfExists
     PRINT 'GO'
print @text
    DELETE FROM @HelpText;

 
END

No comments: