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.
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.
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
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:
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
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?
Original article: Lost in Translation – Deprecated System Tables – syscomments
©2012 Strate SQL. All Rights Reserved.