Storing Stored Procedures

Stored procedures are persistent database objects, and Microsoft SQL Server stores them in virtual objects (that Microsoft used to call system tables and now calls system views or catalog views) to preserve them when their execution plan is removed from the procedure cache or when SQL Server is shut down.

When the Create Procedure statement is executed, SQL Server creates a new record in the sys.objects catalog view (and its equivalent sys.sysobjects) of the current database (see Figure B-l).

Figure B-1: sys.objects

This catalog view contains all schema-bound types of database objects (everything except DDL triggers, which are not schema-bound). Therefore, it is sometimes useful to filter it by object type (using the type field).

The source code of the stored procedure is recorded in the sys. syscomments system table (unless the stored procedure is encrypted). To see the source code, execute sp_helptext or query the sys. syscomments system table directly (see Figure B-2).

Figure B-2: sys.syscomments

The source code is stored in a field named text. The data type of this field is nvarchar (4000). Fortunately, this does not mean that stored procedures are limited to 4,000 characters. If the stored procedure is larger than 4,000 characters, SQL Server allocates additional records with an incremented colid field. Since this field is declared as smallint, a stored procedure can be 32KB x 4,000 bytes = 125MB large. In SQL Server 6.5 and earlier versions, colid was of the data type byte and the text was varchar (2 55), so stored procedures were limited to 255 x 255 = 64KB.

You can hide the source code for a stored procedure if you encrypt it during creation. After you create the stored procedure using With Encryption, none of the users (not even the system administrator) will be able to see it on the server. Keep in mind that you can (and should) keep source code in a separate external script file.

Note 

Before this feature was introduced in SQL Server, developers achieved the same effect by setting the sys.syscomments.text file associated with the stored procedure to null. SQL Server was able to run the stored procedure without any problem. Unfortunately, this solution caused problems during SQL Server upgrades, since setup programs expected to use the text of stored procedures in order to recompile the stored procedures in the new environment. The inclusion of the With Encryption clause eliminated this issue.