Create and Manage SQL Server Stored Procedures using
Transact-SQL
We now know how to return information about our stored procedure, lets look at
the objects that allow us to change a stored procedure's text and basic options
or change how SQL Server sees the stored procedure.
Alter a stored procedure's text:
ALTER PROCEDURE
Data Definition Language statement that alters an existing stored procedure.
ALTER PROCEDURE permissions default to members of the sysadmin server role, the
db_owner and db_ddladmin database roles, and the owner of the procedure and are
not transferable.
Syntax
ALTER PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION
}
]
[ FOR REPLICATION ]
AS
sql_statement [ ...n ]
|
Rename a stored procedure:
SP_RENAME
System stored procedure that changes the name of a user-created object in the
current database.
Permissions default to members of the sysadmin server role, the db_owner and
db_ddladmin database roles, and the owner of the object. Only members of the
sysadmin and dbcreator server roles can execute sp_rename to rename a database.
Syntax
sp_rename @objname = 'object_name' ,
@newname = 'new_name'
, @objtype = 'object_type'
|
Make a stored procedure run on SQL Server start up:
SP_PROCOPTION
System stored procedure located in the master database that sets procedure
options.
Permissions default to members of the sysadmin server roles and are not
transferable.
Syntax
sp_procoption @ProcName = 'procedure'
, @OptionName = 'option'
, @OptionValue = 'value'
|
Make SQL Server think the stored procedure is a system stored procedure:
SP_MS_MARKSYSTEMOBJECT
System stored procedure which sets an object's system bit.
Permissions default to members of the sysadmin server role, the db_owner
database role and the database owner and are not transferable.
Syntax
sp_MS_marksystemobject @objname
|
Some point in your career you will need to know how to go beyond the
information give by the objects and system stored procedures discussed above so
you will do that big no-no in SQL Server and query SQL Server's system tables
directly. Don't worry I do this all the time as sometimes it is the fastest way
to get the information you need. Below are the basic system tables holding
stored procedure information.
System table holding stored procedure's properties:
SYSOBJECTS
System table located in all databases containing one row for each object
created within a database. If the database is the tempdb then it contain
information about each temporary object as well.
|
System table holding stored procedure's text:
SYSCOMMENTS
System table located in all databases containing entries for each view, rule,
default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure in
the database. The text column contains the original SQL definition statements,
which are limited to a maximum size of 4 MB.
|
System table holding stored procedure's parameters:
SYSCOLUMNS
System
table located in all databases containing one row for every column in each
table and each view, and a row for each parameter in a stored procedure.
|
System table holding stored procedure's dependencies:
SYSDEPENDS
System table located in all databases containing dependency information between
objects (views, procedures, and triggers), and the objects (tables, views, and
procedures) contained in their definition.
|
Okay, you now know of all the objects that let you create and manage a stored
procedure object in SQL Server. The next group of objects will let you go one
step further and learn how to manage SQL Server's stored procedure cache.
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 bias 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 aboved referenced
objects can be found in
Transact-SQL Language Reference Guide which is available on my web
site www.TransactSQL.Com.
|
