Create and Manage SQL Server Stored Procedures using Transact-SQL
Randy Dyess, www.TransactSQL.com

As we learn more about SQL Server and Transact-SQL it is easy to become overwhelmed with the amount of information out there. I can recall hundreds of times when I have said, "I know there is a (stored proc, DBCC command, SQL statement, ect) out there that does that, I just can't remember its name." I thought I would deviate from my normal article style and see if writing an article that groups stored procs, DBCC commands, and SQL statements by their main or secondary usage would help others. My current job involves creating and maintaining hundreds of stored procedures so I thought I would put together objects that I knew about that are used in the creation and maintenance of SQL Server stored procedures and extended stored procedures in SQL Server 7.0 and 2000 (I'm not going to list or discuss objects used to obtain users permissions as I feel that those objects are best left for another article).

One of the things we learn as beginning DBAs or Transact-SQL programmers is how to create and delete stored procedures. Later as our skills progress we will learn how to add or delete an association with an extended stored procedure to add a whole new world of functionality to our databases. SQL Server provides multiple documented and undocumented statements and system stored procedures that we can use to create new stored procedures, drop existing stored procedures and associate/disassociate extended stored procedures in our databases (remember extended stored procedure are only maintained in the master database).

Syntax
CREATE PROC [ EDURE ] procedure_name [ ; number ] 
    [ { @parameter data_type } 
        [ VARYING ] [ = default ] [ OUTPUT ] 
    ] [ ,...n ] 
[ WITH 
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 
[ FOR REPLICATION ] 
AS sql_statement [ ...n ] 

Add(associate) a SQL Server extended stored procedure:

DBCC ADDEXTENDEDPROC Database console command which can be used to add an extended procedure. Syntax DBCC ADDEXTENDEDPROC ('@procname', '@dll')

SP_ADDEXTENDEDPROC System stored procedure located in the master database that registers the name of a new extended stored procedure to SQL Server. Permissions default to members of the sysadmin server role and are not transferable. Syntax sp_addextendedproc [ @functname = ] 'procedure' , [ @dllname = ] 'dll'

 

Drop a SQL Server stored procedure:

DROP PROCEDURE Data Definition Language statement used to remove one or more stored procedures or procedure groups in a database. Permissions default to the owner of the stored procedure and members of the sysadmin server role and db_owner and db_ddladmin database roles and are not transferable. Syntax DROP PROCEDURE {procedure} [,…n]

SP_MSDROP_OBJECT System stored procedure which can be used to drop a database object. Permissions default to members of the sysadmin server role and db_owner database role and the owner of the database and are transferable. Syntax sp_MSdrop_object [@object_id] [, @object_name] [, @object_owner]

 

Remove(disassociate) a SQL Server extended stored procedure:

DBCC DROPEXTENDEDPROC Database console command which can be used to drop an extended procedure. Syntax DBCC DROPEXTENDEDPROC('@procedurename')

SP_DROPEXTENDEDPROC System stored procedure that drops an extended stored procedure. Permissions default to members of the sysadmin fixed server role and are not transferable. Syntax sp_dropextendedproc @functname = 'procedure'

 

Now that we have a stored procedure built in SQL Server or a extended stored procedure added to the master database we have several ways to return information about that stored procedure to our users or to our code.

Syntax
sp_stored_procedures [[@sp_name =] 'name'] 
    [,[@sp_owner =] 'owner'] 
    [,[@sp_qualifier =] 'qualifier']

Determine the ID number of the stored procedure

@@PROCID Metadata function returning an INTEGER representing the current stored procedure's identifier number. Syntax @@PROCID

OBJECT_ID Metadata function that returns an INTEGER representing the identification number for the given object name. Syntax OBJECT_ID ('object')

 

Determine the name of a stored procedure given the ID number:

OBJECT_NAME Metadata function that returns a NCHAR representing the database name for the given object identification number. Syntax OBJECT_NAME (object_id)

 

Determine an object dependencies:

SP_DEPENDS System stored procedure which displays information about database object dependencies. Syntax sp_depends [ @objname = ] 'object'

 

Return properties, columns, parameters and the text of a stored procedure:

OBJECT_PROPERTY Metadata function that will return an INTEGER representing information about a specified object in the current database. Syntax OBJECTPROPERTY (id, 'property')

SP_SPROC_COLUMNS System stored procedure which returns column information for a specified stored procedure or user-defined function. Syntax sp_sproc_columns [[@procedure_name =] 'name'] [,[@procedure_owner =] 'owner'] [,[@procedure_qualifier =] 'qualifier'] [,[@column_name =] 'column_name'] [,[@ODBCVer =] 'ODBCVer']

COLUMNPROPERTY Metadata function that returns an INTEGER representing information about a column in a stored procedure or table given the stored procedure or table ID, the column name and the property of the column you are asking about. Syntax COLUMNPROPERTY ( id , 'column' , ' property' )

SP_HELPTEXT System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user- defined function, trigger, or view. Syntax sp_helptext @objname = 'name'

INFOMATION_SCHEMA.PARAMETERS Informational schema view located in the master database (SQL Server 2000) or all databases (SQL Server 7.0) containing one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.

 

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.

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

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.