sys.syscomments (Transact-SQL)

  • Article

Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements. These statements are limited to a maximum size of 4 megabytes (MB).

Important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Column name

Data type

Description

id

int

Object ID to which this text applies.

number

smallint

Number within procedure grouping, if grouped.

0 = Entries are not procedures.

colid

smallint

Row sequence number for object definitions that are longer than 4,000 characters.

status

smallint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

ctext

varbinary(8000)

The raw bytes of the SQL definition statement.

texttype

smallint

0 = User-supplied comment

1 = System-supplied comment

4 = Encrypted comment

language

smallint

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

encrypted

bit

Indicates whether the procedure definition is obfuscated.

0 = Not obfuscated

1 = Obfuscated

ms186293.note(en-US,SQL.90).gifImportant:

To obfuscate stored procedure definitions, use CREATE PROCEDURE with the ENCRYPTION keyword.

compressed

bit

Always returns 0. This indicates that the procedure is compressed.

text

nvarchar(4000)

Actual text of the SQL definition statement.

SQL Server 2005 differs from SQL Server 2000 in the way it decodes and stores SQL expressions in the catalog metadata. The semantics of the decoded expression are equivalent to the original text; however, there are no syntactic guarantees. For example, white spaces are removed from the decoded expression. For more information, see, Behavior Changes to Database Engine Features in SQL Server 2005.

See Also

Reference

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
Compatibility Views (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History
Changed content:
  • Clarified that the encrypted column indicates that the text of a procedure is obfuscated, but not encrypted.

17 July 2006
New content:
  • In the text column, added content about how SQL expressions are stored in SQL Server 2005.