This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view syscomments returns rows for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure in the database.  The rows contain the schema for the objects with each row containing up to 4000 characters of the schema.  If the schema definition exceeds the size limit, then additional rows are used for the object.

The replacement catalog views for syscomments are sys.all_sql_modules and sys.sql_modules.  The catalog views contains generally the same information with one key difference.  Instead of limiting the object definition to 4,000 characters per row, the view uses an nvarchar(max) data type, allowing the entire definition to be returned.  Similar to other catalog views, the “all_” prefix on the one catalog view indicates that the results are returned for all objects in the database versus returning just those for user objects in the other catalog view.

Less Than Useful Columns

There are a few columns in syscomments that are no longer useful with the changes to the catalog view.  For starters, since the entire schema is always in one row, the colid column is no longer needed to string the rows together.  Next the number column, which is used for procedure grouping, is no longer used or needed.  Also, the columns texttype and ctext are no longer included.  The ctext is just a varbinary representation of the schema definition and easy to reproduce.

Along with the other useless columns columns, the status column in syscolumns appears to be fairly useless as well.  Investigating the available flags, the only available bit value appears to be 0X1; which coincides with the use of the ENCRYPTION option.

Query Via syscomments

A typical query against syscomments will include concatenation of the rows for objects with definitions that require multiple rows.  A query with this concatenation is included in Listing 1.  One of the issues with this activity, is that the ctext binary value is no longer accurate for the entire object, but I’m not sure that the column is useful anyways.

Listing 1 – Query for syscomments
SELECT id
,number
,colid
,status
,ctext
,texttype
,language
,encrypted
,compressed
,text
,(SELECT text + SPACE(1)
FROM syscomments c2
WHERE c1.id = c2.id
ORDER BY colid
FOR XML PATH(''))
FROM syscomments c1
WHERE colid = 1

Additional Information

The introduction of the catalog views allows for some additional information to be retrieved about schema objects which were previously difficult to ascertain.  These are available in the following columns:

• uses_ansi_nulls: Identifies if the object was created with ANSI_NULLS set
• uses_quoted_identifier: Identifies if the object was created with QUOTED_IDENTIFIER set
• is_schema_bound: Identifies if the object was created with schema binding
• uses_database_collation: Identifies whether the object uses the database collation
• is_recompiled: Identifies if the object was created with recompile
• null_on_null_input: Identifies if the object uses null input and output parameters
• execute_as_principal_id: Identifies if the object uses EXECUTE AS and how that it is implemented.

Query via Catalog Views

To retrieve similar information to that in syscomments from the new catalog views, use the query provided in Listing 2.  The OBJECTPROPERTY function is used to determine whether the object definition is encrypted, since this column is not included in the new catalog views.  As mentioned previously, there are two versions of the catalog view, one for all objects and another for user-defined objects.  To return only those for user-defined objects, use sys.sql_modules in the query instead of sys.all_sql_modules.

Listing 2 – Query for schema definition
SELECT sm.object_id AS id
,IIF(o.type = 'P',1,0) AS number
,CONVERT(varbinary(max),sm.definition) AS ctext
,OBJECTPROPERTY(sm.object_id,'IsEncrypted') AS encrypted
,0 AS compressed
,sm.definition AS text
,sm.uses_ansi_nulls
,sm.uses_quoted_identifier
,sm.is_schema_bound
,sm.uses_database_collation
,sm.is_recompiled
,sm.null_on_null_input
,sm.execute_as_principal_id
FROM sys.all_sql_modules sm
INNER JOIN sys.all_objects o ON sm.object_id = o.object_id

Summary

In this post, we discussed the use of syscomments and how it has been replaced with catalog views sys.all_sql_modules and sys.sql_modules.  The use of the catalog views provides an easier interface for retrieving schema since concatenation of the rows is not required.

Do you see any reason to continue using syscomments ?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

1. Lost in Translation – Deprecated System Tables – sysconstraints
2. Lost in Translation – Deprecated System Tables – sysconfigures
3. Lost in Translation – Deprecated System Tables – syscurconfigs

Original article: Lost in Translation – Deprecated System Tables – syscomments

©2012 Strate SQL. All Rights Reserved.