>Is there anyway to recompile all procedures and UDF’s all at once and get an error report?
>
>We have 3480 procedures and 179 UDF that I would like to make sure that are all clean. How can I recompile these procedures programmatically immediately?

Mike,

If you are going to recompile the objects, you will want to determine the parameters that are most representative of everyday operation for each object. SQL Server will use whichever parameters are passed the first time the proc or function is executed to compile the execution plan. If these parameters are not optimal, performance will suffer during everyday operation.

See sp_recompile to see how to mark each object for recompilation.

declare Procs_cursor cursor fast_forward
for select [name] from sysobjects 
where OBJECTPROPERTY(id, N'IsProcedure') = 1 OR OBJECTPROPERTY(id, N'IsScalarFunction') = 1

declare @ObjectName varchar(128)

open Procs_cursor
fetch next from Procs_cursor into @ObjectName

while @@FETCHSTATUS = 0
begin
EXEC sp_recompile @ObjectName

fetch next from Procs_cursor into @ObjectName
end

close Procs_cursor
deallocate Procs_cursor