This stored procedure, updates the schema version of user table(s), which in turn marks the Stored Procedures and Triggers for recompile.
CREATE procedure dbo.P_ZDBA_Recompile @dbname varchar(30)
As
Set NoCount On
declare @strmsg nvarchar(2000),
@strdbname varchar(30)
Set @strdbname = @dbname
Select @strmsg = N'USE ' + @strdbname + char(13) + N'exec
sp_MSforeachtable "DBCC LockObjectSchema (''?'')",
@whereand="and exists (select name from sysobjects
where o.type = ''U'')"'
exec sp_executesql @strmsg
Return(@@error) --end of P_ZDBA_Recompile 
When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.
2018-05-18 (first published: 2015-10-19)
4,855 reads
If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.
2011-08-18
5,492 reads
Far too often, people who are not trained database administrators somehow end up responsible for a database. They lack the proper training and knowledge to maintain their database, and problems begin to develop. Here’s a primer for all those involuntary DBAs who need a crash course in database maintenance best practices.
2008-08-13
6,696 reads
This is a script, similar to some you have seen in the past, that will monitor file growth on all databases for the given server. This script is not a fancy solution that has a tons of bells and whistles but, it is efficient. The script is a concantanation of the create table / populate […]
2001-12-21
3,370 reads
This script will change the owner of the stored procedure to dbo. It must be run in the same database as the stored procedures.
2001-10-28
1,599 reads