获取数据库内某时间点后修改的存储过程

最新推荐文章于 2022-05-09 09:25:37 发布

weixin_34278190

最新推荐文章于 2022-05-09 09:25:37 发布

阅读量102

点赞数

文章标签：数据库

declare @procname nvarchar(100)=N'';
declare curProc cursor for
select 
 [name]
 --,create_date
 --,modify_date 
FROM 
 sys.all_objects 
where 
 type_desc = N'SQL_STORED_PROCEDURE' 
 and substring([name],1,3) not in ('sp_','dt_','xp_')
 and modify_date >='2012-01-02 11:12:00'
order by 
 modify_date desc;

open curProc;
fetch next from curProc into @procname;
while @@FETCH_STATUS = 0 
begin
 print(N'IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE [NAME]=N''' + @procname +''' AND [TYPE]=''P'')')
 PRINT(N'DROP PROCEDURE ' + @procname);
 print N'GO';
 exec ('sp_helptext ' + @procname);
 print N'GO';
 fetch next from curProc into @procname;
end
close curProc;
deallocate curProc;