vk-kirov (3/1/2010)
Oleg Netchaev (3/1/2010)
there are a couple of records inserted into the system tables when the procedure is created. The procedure name is inserted into sysobjects and the text of the stored procedure is inserted into syscomments.Syscomments and sysobjects are not system tables anymore (in MSSQL 2005 and higher). They are views included for backward compatibility only (BOL: http://msdn.microsoft.com/en-us/library/ms177596.aspx, http://msdn.microsoft.com/en-us/library/ms186293.aspx). The 'sys.syscomments' view is quite complicated, and it queries data from several system tables.
Beware of using those views in new projects, use sys.objects and sys.sql_modules instead 🙂
Sorry, this is my bad, I forgot to mention that this is only applicable to the SQL Server 2000. I understand that the situation in 2005/2008 is different. The actual data is stored in the base system table named sys.sysschobjs. The direct access to this table is not available except through DAC, and therefore, the object related data is instead exposed through the system views like the ones you mention. The bottom line is the same: there are records inserted into the system tables the moment the proc is created. So, to access the text of the proc via sys.objects one can use something like this:
select
[object_id], object_name([object_id]) [object_name],
object_definition([object_id]) definition
from sys.objects
where [object_id] = object_id('[your_schema].[your_proc_name]');
and via sys.sql_modules like this:
select
[object_id], object_name([object_id]) [object_name], definition
from sys.sql_modules
where [object_id] = object_id('[your_schema].[your_proc_name]');
Oleg