Recompile / Refresh SQL Objects

Script to refresh views and force recompile of stored procedures.

--declare 
declare @cmd varchar(1000)
 
-- declare some variables to hold the data from the cursor
declare @name sysname
   , @schema sysname


   
print ''
print ''
print 'Refreshing views' 
print ''


-- declare & open a cursor to get the names of the views in the database.
DECLARE curViews cursor fast_forward for
   select nm = OBJECT_NAME(a.[Object_id])
      , sch = OBJECT_schema_name(a.[Object_id])
   from sys.views a
   where a.is_ms_shipped = 0 -- do NOT include objects shipped by microsoft
OPEN curViews
 
-- get the 1st record from the cursor.
FETCH next from curViews into @name, @schema
 
-- keep going while the last fetch was good
WHILE (@@FETCH_STATUS = 0)
begin
 
   -- build our command statement to refresh the view
   set @cmd = 'sp_refreshview ''' + quotename(@schema) + '.' + quotename(@name) + ''''


   print 'running: ' + @cmd
   exec (@cmd)

 
   -- get the next record from the cursor
   FETCH next from curViews into @name, @schema
end
 
-- close & deallocate the cursor
CLOSE curViews
DEALLOCATE curViews



print ''
print ''
print 'Recompiling stored procedures' 
print ''

-- declare a cursor to get all the user stored procedures
DECLARE curProcs cursor fast_forward for
   select 
      nm = OBJECT_NAME(a.[Object_id])
      , sch = OBJECT_schema_name(a.[Object_id])
   from sys.procedures a
   where a.is_ms_shipped = 0 -- do NOT include objects shipped by microsoft
OPEN curProcs
 
-- get the 1st record from the cursor.
FETCH next from curProcs into @name, @schema
 
-- keep going while the last fetch was good
WHILE (@@FETCH_STATUS = 0)
begin
 
   -- build our command statement to recompile the stored procedure
   set @cmd = 'sp_recompile  ''' + quotename(@schema) + '.' + quotename(@name) + ''''


   print 'running: ' + @cmd
   exec (@cmd)

 
   -- get the next record from the cursor
   FETCH next from curProcs into @name, @schema
end
 
-- close & deallocate the cursor
CLOSE curProcs
DEALLOCATE curProcs