Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

sys.syscomments (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

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.

System_CAPS_importantImportant

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you use sys.sql_modules instead. For more information, see sys.sql_modules (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version).

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

System_CAPS_importantImportant

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.

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.

This SQL Server 2000-compatible view obtains information from current SQL Server structures and can return more characters than the nvarchar(4000) definition. sp_help returns nvarchar(4000) as the data type of the text column. When working with syscomments consider using nvarchar(max). For new development work, do not use syscomments.

Community Additions

ADD
Show:
© 2015 Microsoft