Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

Create and Manage SQL Server Stored Procedures using Transact-SQL

By : Randy Dyess
Jun 29, 2002

Page 3 / 4

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
sp_MS_marksystemobject @objname

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.

<< Prev Page Next Page>>