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 at 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 |
At 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.