Create and Manage SQL Server Stored Procedures using Transact-SQL

System Table Holding Procedure Cache Information

SYSCACHEOBJECTS
System table located in the master database containing information about how the cache is used.

 

Return Information on Procedure Cache

DBCC PROCCACHE
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.

Syntax
DBCC PROCCACHE

 

Remove Execution Plans from Procedure Cache

SP_RECOMPILE
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.

Syntax
sp_recompile @objname = ‘object’

DBCC FREEPROCCACHE
Database console command that removes all elements from the procedure cache.
Permissions default to members of the sysadmin and serveradmin server roles.

Syntax
DBCC FREEPROCCACHE

DBCC FLUSHPROCINDB
Database console command which can be used to force a recompile of all the stored procedures in a database.

Syntax
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.

Detailed syntax information and examples on each of the above referenced objects can be found in Transact-SQL Language Reference Guide which is available on my web site at www.TransactSQL.Com.

Published with the express written permission of the author. Copyright 2002 by the author.

]]>

Leave a comment

Your email address will not be published.