Tuesday, December 26, 2006

SysComments v/s sys.sql_modules - Returns different result for same query

In SQL 2000 we used to query system table SysComments to get the SQL Definition of Objects like SP,Views,Trigger,Functions etc. It seems that the counterpart in SQL 2005 is sys.sql_modules. But when i ran same query for SysComments and sys.sql_modules, the rows affected was different(No. of Rows). Out of curiosity, i thought it need a postmortem. When i ran the following query in my R&D database , the SysComments returned 138 rows and sys.sql_modules returned 84 rows.

SELECT obj.name , mod.definition,obj.object_id,len(mod.definition) FROM sys.sql_modules mod INNER JOIN sys.objects obj ON mod.object_id = obj.object_id WHERE obj.type = 'p'

SELECT o.name, m.Text ,o.Object_ID, m.id,len(m.Text) FROM syscomments m INNER JOIN sys.objects o ON m.id = o.object_id WHERE o.type = 'p'

You know why this different result for same query; it because in SQL Server we have new Data Type called Nchar/nvarchar (Max) etc. So in SQL 2005 the SQL Definition is stored in nvarchar(Max) data type where as in SQL Server 2000 it is nvarchar(4000). So you will have different result when you have a large stored procedure definition.


Post a Comment

Subscribe to Post Comments [Atom]

<< Home