Create and Manage SQL Server Stored Procedures using Transact-SQL
System Table Holding Procedure Cache Information
System table located in the master database containing information about how the cache is used.
Return Information on Procedure Cache
Database console command that displays information about the procedure cache.
Permissions default to members of the sysadmin server role and db_owner database role and are not transferable.
Remove Execution Plans from Procedure Cache
System stored procedure that causes stored procedures and triggers to be recompiled the next time they are run. Permissions default to members of the sysadmin server role, the db_owner database role, and the owner of the object and are not transferable.
sp_recompile @objname = ‘object’
Database console command that removes all elements from the procedure cache.
Permissions default to members of the sysadmin and serveradmin server roles.
Database console command which can be used to force a recompile of all the stored procedures in a database.
DBCC FLUSHPROCINDB (@dbid)
Using the above described objects, you can efficiently create and manage both stored procedures and extended stored procedures in SQL Server without having to rely on SQL Server’s or a third-party GUI interface. I’m a little biased in my belief that what separates a true DBA from someone who just manages SQL Server is the ability to operate without all the GUI screens and wizards that Microsoft has so kindly built for us in SQL Server.
Published with the express written permission of the author. Copyright 2002 by the author.