syscomments

All databases

Description

syscomments contains entries for each view, rule, default, trigger, table constraint, partition, procedure, computed column, function-based index key, and other forms of compiled objects. The text column contains the original definition statements. If the text column is longer than 255 bytes, the entries span rows. Each object can occupy as many as 65,025 rows.

It also stores the text of a computed column, function-based index, or partition definition—for example, “values <= value_list” for a range partition.

The create service command stores text in syscomments, as it uses the create procedure infrastructure.

Columns

The columns for syscomments are:

Name	Datatype	Description
id	int	Object ID to which this text applies.
number	smallint	Sub-procedure number when the procedure is grouped (0 for nonprocedure entries).
colid	smallint	The low portion of a column counter for this procedure’s comments. Can vary from 0 to 32767. If a procedure has more text than fits in that many rows, this counter works together with colid2.
texttype	smallint	Indicates the comment type. Values are: 0 – system-supplied comment, for views, rules, defaults, triggers, and procedures 1 – user-supplied comment for adding entries that describe an object or column
language	smallint	Reserved.
text	varchar(255) null	Actual text of SQL definition statement.
colid2	smallint	The high portion of a column counter for this procedure’s comments. Can vary from 0 to 32767. Is only greater than 0 for procedures containing more than 32,768 rows of procedure text.
status	smallint null	Bits indicating the status of the objects: 0x1 – SYSCOM_TEXT_HIDDEN indicates that the text is hidden 0x2 – Reserved for internal use 0x4 – SYSCOM_QUOTED_ID_ON indicates that quoted identifiers were on when the object was created
partitionid	int null	Partition ID. Otherwise, null.

Do not delete the definition statements from the text column of syscomments. These statements are required for the Adaptive Server upgrade process. To encrypt a definition statement, execute the system procedure sp_hidetext. To see if a statement created in version 11.5 or later was deleted, execute sp_checksource. If the statement was deleted, you must either re-create the object that created the statement or reinstall the application that created the object, which re-creates the statement.

You can protect the text of a database object against unauthorized access by restricting select permission on the text column of the syscomments table to the owner of the object and the System Administrator. This restriction, which applies to direct access through select statements as well as access through stored procedures, is required to run Adaptive Server in the evaluated configuration. To enact this restriction, a System Security Officer must reset the parameter called allow select on syscomments.text column using the system procedure sp_configure. For information about the evaluated configuration, see the System Administration Guide.

Indexes

Unique clustered index on id, number, colid2, colid, texttype