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

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;

 

posted on 2013-05-10 13:26  freeliver54  阅读(367)  评论(1编辑  收藏  举报

导航