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
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:
Post a Comment