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:

  • 0x1SYSCOM_TEXT_HIDDEN indicates that the text is hidden

  • 0x2 – Reserved for internal use

  • 0x4SYSCOM_QUOTED_ID_ON indicates that quoted identifiers were on when the object was created

partitionid

int null

Partition ID. Otherwise, null.

NoteDo 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